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

[Sokkorn]

Hi Rick,

Here is an idea for load script

[MaxDateTable]:

LOAD Max(LastModify) AS [MaxDate] FROM Transaction.qvd (qvd);

Let vMaxDate = Date(Peek('MaxDate',0,'MaxDateTable'),'YYYY-MM-DD');

//LET vMaxDate = Date(Peek('MaxDate',0,'MaxDateTable'),'DD-MM-YYYY');

//LET vMaxDate =TIMESTAMP(Peek('MaxDate',0,'MaxDateTable'),'YYYY-MM-DD hh:mm:ss.fff');

DROP TABLE [MaxDateTable];

[TableSQL]:

SQL Select * FROM tblName WHERE LastModify > '$(vMaxDate)';

//SQL Select * FROM tblName WHERE LastModify > CHR(39) & $(vMaxDate) & CHR(39);

*** You may test it line by line via "Comment" and "Uncomment"

Can you share your script here?

Regards,

Sokkorn

[#3]

Hi Rick , I am suspecting, the problem with variable conversion into your date field datatype and syntax will change based on the DB Type.

If the DB is SQL Server ,  you don't to convert the variable into date format

          WHERE DATE_FIELD >= '$(vMaxDate)'

If the DB is Oracle, the syntax will depend on the DATE_FIELD data type.

     1. If the DATE_FEILD data type is DATE then,

           LET vMaxDate = Date(Peek('MAX_DATE') , 'YYYY-MM-DD') ;

           SELECT * FROM TABLE WHERE DATE_FIELD >= TO_DATE('$(vMaxDate)' , 'YYYY-MM-DD') ;

     2. If the DATE_FEILD data type is TIMESTAMP then,

           LET vMaxDate = Date(Peek('MAX_DATE') , 'YYYY-MM-DD hh:mm:ss') ;

           SELECT * FROM TABLE WHERE DATE_FIELD >= TO_DATE('$(vMaxDate)' , 'YYYY-MM-DD HH24:MM:SS') ;

Note: First test your SQL Where cond query outside qlikview like TOAD or any tool then use in Qlikview.

[#4]

Thank you Sokkorn and Dathu for your helpful tips.

I can confirm that the SQL select statement works as intended in HeidiSQL, however when running via QV, it is erring as though [where Date > '$(vMaxNPS)';] is blank  (returns syntax equivalent to [where Date > '']

I get the feeling that the problem may lie in the variabel itself. When I call the field name in a text box (MaxNPSDate) i get the 5 digit value...but when i can the variable $(vMaxNPS) I get nothing.

[#5]

I can confirm that the SQL select statement works as intended in HeidiSQL, however when running via QV, it is erring as though [where Date > '$(vMaxNPS)';] is blank  (returns syntax equivalent to [where Date > '']

I get the feeling that the problem may lie in the variabel itself. When I call the field name in a text box (MaxNPSDate) i get the 5 digit value...but when i can the variable $(vMaxNPS) I get nothing.

[#6]

I can confirm that the SQL select statement works as intended in HeidiSQL, however when running via QV, it is erring as though [where Date > '$(vMaxNPS)';] is blank  (returns syntax equivalent to [where Date > '']

I get the feeling that the problem may lie in the variabel itself. When I call the field name in a text box (MaxNPSDate) i get the 5 digit value...but when i can the variable $(vMaxNPS) I get nothing.

[Sokkorn]

Hi Rick,

Can you share your script. So that we can find it out together; spot on why vMaxNPS return nothing.

Regards,

Sokkorn

[#8]

Hi Sokkorn

I've had to remove field names due to sensitivity, but here is the script. I also seem to have another problem in storing the newly acquired data into a separate qvd.

//Load previos qvd file

NPS:

LOAD [8 Data Fields]

     FILE_DATE,

     [5 Fact Fields]

FROM

nps.qvd

(qvd);

//Set max data date for file in sql format. This returns '2014-06-01' which is acceptable for MySQL however when calling the variable returns blank.

MaxNPSTable:

LOAD

  Date(max(FILE_DATE),'YYYY-MM-DD') as MaxNPSDate

FROM NPS.qvd(qvd);

//set variable to the max data date

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

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

[5 fact fields],

[8 data Fields]

FROM dashboarddata.nps

where Date > '$(vMaxNPS)';

Store NPSNEW into NPSNEW.qvd(qvd);

[Sokkorn]

Hi Rick,

Can you try this one

[MaxNPSTable]:

LOAD

  Max(FILE_DATE) AS [MaxNPSDate]

FROM NPS.qvd (qvd);

//set variable to the max data date

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

Edit: Changed log

1. Changed Date(max(FILE_DATE),'YYYY-MM-DD') to Max(FILE_DATE)

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

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

Regards,

Sokkorn

[#10]

Thanks Sokkorn

I aplpied your changes and it has given much the same result. MaxNPSDate is equal to the excel equivalent of 6th january (41645) however when calling the variable within a text object to test it the result is 2007. (within the text object is =$(vMaxNPS)

also, when running the SQL extract it retrieves the entire table and appends it to the existing dataset within the QVW. I will try some more tomorrow...this one will not beat me!!