Inconsistent datatypes: expected NUMBER got CHAR at OCI call OCIStmtExecute — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Inconsistent datatypes: expected NUMBER got CHAR at OCI call OCIStmtExecute

Received Response
688
Views
3
Comments
3174977
3174977 Rank 5 - Community Champion

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!

Answers

  • What is your CALENDAR_WEEK column?

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

  • 3174977
    3174977 Rank 5 - Community Champion

    Calendar_Week is a varchar column

  • Joel
    Joel Rank 8 - Analytics Strategist

    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'.