Inconsistent datatypes: expected NUMBER got CHAR at OCI call OCIStmtExecute

Question

Hi All,

I am using the below case satement in my Logical column in RPD and when I am using this column in answers I am getting the below error.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 932, message: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

Below is my case statement which I am using.

CASE WHEN   "Planner WorkBench"."DW_SUPPLY_DEMAND"."PROJ_OUT_DATE" <= "Planner WorkBench"."DW_DAY_D"."CALENDAR_DT" then  "Planner WorkBench"."DW_SUPPLY_DEMAND"."QUANTITY"

when  "Planner WorkBench"."DW_SUPPLY_DEMAND"."PROJ_OUT_DATE" >= "Planner WorkBench"."DW_DAY_D"."CALENDAR_DT" then  "Planner WorkBench"."DW_DAY_D"."CALENDAR_WEEK"

else 0

end

All my dates datatype are datetime & quantity datatype is Double..

Is something missing in my case statement

Thanks!

Joel Acha · Answer

Your CASE statement "return values" should be of a consistent data type.  In your case, you're returning a VARCHAR, NUMBER & a DOUBLE.

I suggest that you convert your "return values" to a consistent data type, in your case, convert quantity by doing this:

CAST(Planner WorkBench"."DW_SUPPLY_DEMAND"."QUANTITY"  AS CHAR)

and replace 0 with '0'.

3174977 · Answer

Calendar_Week is a varchar column

Gianni Ceresa · Answer

What is your CALENDAR_WEEK column?

Because your CASE can return a double (QUANTITY), a xxxxx (CALENDAR_WEEK) or a number (the ELSE).