ORA-00932: inconsistent datatypes: expected DATE got NUMBER

[vireshkolagimat]

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

[Anil_Babu_Samineni]

Try with red part

$(_qvd_name):

SQL SELECT *

FROM $(_table_name)

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

[vireshkolagimat]

Hi, Getting below error message.

ORA-00904: "FORMAT": invalid identifier

Regards,

Viresh

[Anil_Babu_Samineni]

Can you trace variables and share image of format?

[vireshkolagimat]

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.

[vireshkolagimat]

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

Thank you.

[quwok]

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?