26 Replies Latest reply: Jun 5, 2014 3:11 AM by Jose Maria Cerezo Lopez RSS

    Using a variable within SQL select statement

    Rick Green

      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

        • Re: Using a variable within SQL select statement
          Sokkorn Cheav

          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

            • Re: Using a variable within SQL select statement
              Rick Green

              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.

                • Re: Using a variable within SQL select statement
                  Sokkorn Cheav

                  Hi Rick,

                   

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

                   

                  Regards,

                  Sokkorn

                    • Re: Using a variable within SQL select statement
                      Rick Green

                      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);

                        • Re: Using a variable within SQL select statement
                          Sokkorn Cheav

                          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

                            • Re: Using a variable within SQL select statement
                              Rick Green

                              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!!

                    • Re: Using a variable within SQL select statement
                      Srikanth P

                      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.

                        • Re: Using a variable within SQL select statement
                          Rick Green

                          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.

                        • Re: Using a variable within SQL select statement
                          Rick Green

                          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.

                          • Re: Using a variable within SQL select statement
                            Rick Green

                            All

                             

                            I want to thank you for all of your help...looks like the problems I was having was in thinking that I had to load the previous .qvd file before attempting to set the variable. I accidentally commented that out and it now runs ok

                             

                            so Thank you Sokkorn, Dathu and Michael...we have a fantastic community here!!

                            • Re: Using a variable within SQL select statement
                              Jose Maria Cerezo Lopez

                              Hi All,

                               

                                  I'm having a problem with a variable inserted in the FROM line in a SQL SELECT sentence accesing to a MDB database ... With the red line I get a ODBC driver error .... but the variable is replaced correctly and the text are the same in booth cases.

                               

                              Thanks.

                               

                              Snap36.jpg