Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Enthu
Creator II
Creator II

Comparing two dates in where clause of load statement

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?

9 Replies
divya_anand
Creator III
Creator III

Hi Saimeenakshi,

Would it be possible to share a sample QVW?

Qlik_Enthu
Creator II
Creator II
Author

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
Creator III
Creator III

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
Creator II
Creator II
Author

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
Creator III
Creator III

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
Creator III
Creator III

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

Qlik_Enthu
Creator II
Creator II
Author

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

divya_anand
Creator III
Creator III

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
Creator II
Creator II
Author

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!