Using a variable within SQL select statement

[#1]

Hi All,

My first foray into incremental loads is not going as planned I have used the following steps and am now somewhat stuck.

1. load previous qvd file

2. Create new table with max date of file

3. Set variable to equal max data from file (using let and peek statement)

all of this works fine...i will drop the temp table once I can get this to work...my problem is getting the SQL Select (via ODBC connection) to recognise the variable within the where statement. I have tried a few different methods (preceding load, Setting the variable, letting the variable, adding quatations, removing quotations, but I cant get the syntax right. Any help would be greatly appreciated

[#2]

As per your comments, the variable didn't have the date field,

Use the TRACE command to show the value of variable in the load script and also check the log file after fail, then you can see which value holding in the variable.

TRACE Max Date value ----> $(vMaxDate);

Can you please post your Variable definition in LET statement ?

[#3]

Hi Dathu

I have tried a couple of variations for the LET statement

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD'); returns 2007 for some reason

LET vMaxNPS = Date(Peek(MaxNPSDate,0,MaxNPSTable),'YYYY-MM-DD'); returns -

[#4]

TRACE of the vMaxNPS shows blank

and when I debug, the LET statement appears to read the field MaxNPSDate from the MaxNPSTable as NULL

[#5]

Can you please post whole script what are you using.

I am suspecting the problem with MaxNPSTable and how many values exist in this table?

This table must have only one value and that would be maximum date value. Please dont drop the table and check what is the value on the table ? In the this table having only one date value then there shouldn't be a problem.

[#6]

Hi Dathu

The table only contains a single value

Here is the complete script

NPS:

LOAD

FILE_DATE,

FROM

nps.qvd

(qvd);

MaxNPSTable:

LOAD

Max(FILE_DATE) AS

FROM NPS.qvd(qvd);

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

,

FROM dashboarddata.nps

where Date > $(vMaxNPS);

Rick

[#7]

Try like below:

NPS:

LOAD

     FILE_DATE,

FROM nps.qvd (qvd);

MaxNPSTable:

LOAD Max(FILE_DATE) AS MaxNPSDate

FROM NPS.qvd(qvd);

LET vMaxNPS = Date(Peek('MaxNPSDate'),'YYYY-MM-DD');

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

,

FROM dashboarddata.nps

where Date > '$(vMaxNPS)';

if not work, what is value you are getting in the MaxNPSTable (date or string) ?

[Sokkorn · Accepted solution]

Hi Rick,

Please load two statement below only and give me the result:

MaxNPSTable:

LOAD Max(FILE_DATE) AS [MaxNPSDate] FROM NPS.qvd (qvd);

// What is the value of field [MaxNPSDate]?

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

// What is the value of variable vMaxNPS?

Awaiting your prompt response.

Regards,

Sokkorn

[#9]

Hi Sokkorn

MaxNPSTable:

LOAD Max(FILE_DATE) AS FROM NPS.qvd (qvd);

Returns a value of 41645 (excel equivalent of 6th Jan 2014)

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

When specified as =vMaxNPS shows 2014-01-06 but when specified as =$(vMaxNPS) shows 2007

Rick

[Sokkorn]

Hi Rick,

Here is my test

Result after load

You see the line that I use TRACE function with $(vMaxNPS), then it return 2014-01-06. Not sure why you got 2007

Regards,

Sokkorn

[#11]

Hi Rick, can you check what is your SET DateFormat variable ?

Try Like below:

LET vMaxNPS  =  text(Date(Peek('MaxNPSDate'),'YYYY-MM-DD')) ;

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

,

FROM dashboarddata.nps

where Date > '$(vMaxNPS)';