Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!