Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
vireshkolagimat
Creator III
Creator III

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Hi All,

I am loading data from Oracle db and getting the above message.

the query looks like below:

$(_qvd_name):

SQL SELECT *

FROM $(_table_name)

where $(vUpdateField) > $(vMaxKey);

after evaluation it looks like below:

SQL SELECT *

FROM apps.xxegc_qv_sales_history_v

where INVOICEDATE > 26/12/2017

My vMaxKey is the format 26/12/2017(DD/MM/YYYY) the default date format used in Qlikview. But when i try to load the data from oracle ( the invoicedate is stored in DD-MMM-YY format) and my script is failing.

I tried to use to_date function but still getting error.

Thanks for your support.

Regards,

Viresh

8 Replies
Anil_Babu_Samineni

Try with red part

$(_qvd_name):

SQL SELECT *

FROM $(_table_name)

where $(vUpdateField) > FORMAT($(vMaxKey),'DD/MM/YYYY');

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vireshkolagimat
Creator III
Creator III
Author

Hi, Getting below error message.

ORA-00904: "FORMAT": invalid identifier

Regards,

Viresh

Anil_Babu_Samineni

Can you trace variables and share image of format?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vireshkolagimat
Creator III
Creator III
Author

Hi, please find the below script.

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "FORMAT": invalid identifier


data:

SQL SELECT *

FROM apps.xxegc_qv_sales_history_v

where INVOICEDATE > FORMAT('27/12/2017','DD/MM/YYYY')


And also Maxkey is stored as number.

Anil_Babu_Samineni

How you are defining your variables? Please post all possible inputs instead asking one by one.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vireshkolagimat
Creator III
Creator III
Author

Hi, Please find the attached QVW file and let me now if you need any further questions.

Thank you.

quwok
Creator III
Creator III

The Oracle function to convert to date should be TO_DATE

Anil_Babu_Samineni

I have seen this $(vUpdateField) as INVOICE DATE, But there In Data model there is no field such.. Not sure how you are restrict the data here?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful