3 Replies Latest reply: Aug 7, 2017 4:36 AM by Chris Wong RSS

    No data using date in oracle DB where clause

    Mona Ali

      Hi all,

      I am using ODBC Connection to Oracle DB and need to apply incremental load using date

       

      I have written the following code

       

      MaxDateTable:

      LOAD Max(CREATION_DATE) AS MaxDate FROM [filename.qvd](qvd);

       

      LET vMaxCrtDate = Date(Peek('MaxDate',0,'MaxDateTable'),'DD/MM/YYYY hh:mm:ss');

       

      [Updatedtable]:

       

      LOAD

       

      fld1,

      fld2;

         

      SQL

      SELECT fld1,fld2

      FROM tablename

      where CREATION_DATE = TO_DATE('$(vMaxCrtDate)', 'DD/MM/YYYY HH24:MI:SS');


      The equals operator in where clause does not retrieve any data

      in addition when replace where clause with

      TRUNC(CREATION_DATE) = '29/04/2011'

      no data retrieved despite same query run on TOAD and 43 records retrieved.

      any help? thanks in advance