topic ORA-00932: inconsistent datatypes: expected DATE got NUMBER in QlikView

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

vireshkolagimat — Tue, 26 Dec 2017 14:35:12 GMT

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

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Anil_Babu_Samineni — Tue, 26 Dec 2017 15:03:13 GMT

Try with red part

$(_qvd_name):

SQL SELECT *

FROM $(_table_name)

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

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

vireshkolagimat — Wed, 27 Dec 2017 04:49:25 GMT

Hi, Getting below error message.

ORA-00904: "FORMAT": invalid identifier

Regards,

Viresh

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Anil_Babu_Samineni — Wed, 27 Dec 2017 05:20:18 GMT

Can you trace variables and share image of format?

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

vireshkolagimat — Wed, 27 Dec 2017 08:34:57 GMT

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.

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Anil_Babu_Samineni — Wed, 27 Dec 2017 08:59:15 GMT

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

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

vireshkolagimat — Wed, 27 Dec 2017 09:57:23 GMT

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

Thank you.

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

quwok — Wed, 27 Dec 2017 10:16:18 GMT

The Oracle function to convert to date should be TO_DATE

Re: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Anil_Babu_Samineni — Wed, 27 Dec 2017 12:24:15 GMT

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?