Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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)
Highlighted
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.

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)