Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

vireshkolagimat
Contributor 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

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

Try with red part

$(_qvd_name):

SQL SELECT *

FROM $(_table_name)

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vireshkolagimat
Contributor III

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

Hi, Getting below error message.

ORA-00904: "FORMAT": invalid identifier

Regards,

Viresh

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

Can you trace variables and share image of format?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vireshkolagimat
Contributor III

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

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vireshkolagimat
Contributor III

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

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

Thank you.

Highlighted
quwok
Contributor III

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

The Oracle function to convert to date should be TO_DATE

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

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)