Comparing two dates in where clause of load statement

[Qlik_Enthu]

Hi all,

I am trying to compare the source data date against the sysdate and written the below in where clause:

And DATE("CUSORD_DATE",'M-D-YYYY') >= DATE("SYSDATE"-1,'M-D-YYYY');

however I am getting error message:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

SQL SELECT * FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' And DATE("CUSORD_DATE",'M-D-YYYY') >= DATE("SYSDATE"-1,'M-D-YYYY')

Can anyone correct me if there is a mistake? also should we use any other table to include SYSDATE?

[divya_anand]

Hi Saimeenakshi,

Would it be possible to share a sample QVW?

[Qlik_Enthu]

Hello Divya,

Just started with the QVW creation and data load. my QV Script has only one load statement with a where clause. Below is my partial load statement:

SQL SELECT * FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' And DATE("CUSORD_DATE",'M-D-YYYY') >= DATE("SYSDATE"-1,'M-D-YYYY');

[divya_anand]

Hi Saimeenakshi,

I assume CUSORD_DATE is from "COS.CUSTOMER_ORDER". What about SYSDATE? Have you assigned a value to SYSDATE prior to the SQL Statement?

You may try and share the application even if there is no data or connection string.

[Qlik_Enthu]

I have not assigned any value to Sysdate (assuming it will store today`s date from the system tables) now I think that's not correct. I try copy pasting my .qvw, but its not working nor I find an attachment option while replying to the thread...

[divya_anand]

You should be able to attach and application using the "App" icon-

If you don't find it, please use the Advanced Editor-

[divya_anand]

You should be using "today()" to fetch today's date.

[Qlik_Enthu]

I don't find the attachment option in !App nor the advance editor feature 

[divya_anand]

You might want to try this when you are free-

And in the meantime,

It is difficult to help without a sample application or without a clear picture of the script you are using. However, you may try this and see if this works for your scenario-

Assuming that the Date format in the database is 'M-D-YYYY'

let vDate  = Timestamp(Today(),'M-D-YYYY');

LOAD *;

SQL SELECT * FROM COS.CUSTOMER_ORDER

WHERE "CUSORD_STAT" ='00' And

CUSORD_DATE >= '$(vDate)';

[Qlik_Enthu]

Hi Divya,

I somehow managed to do it by my self. below is my code:

LET vToday=Date(Today(1),'MM/DD/YYYY');

ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is RQNcTBdJTSaA, XPassword is efNSaBdJTaNOTbAN);
//-------- Start Multiple Select Statements ------
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
Date(CUSORD_DATE,'MM/DD/YYYY') As ORDER_DATE,
CUST_NO,
CUS_NAME,
CTY_CODE;
SQL SELECT * FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "CUSORD_NO">=$(vToday);

Thank you for helping me on this!