CODEWITHSUNDEEP
sqloracle-databaseoracle-apex-5
JB999
JB999

Reputation: 507

Oracle Apex - ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

I'm getting the ORA-00932: inconsistent datatypes: expected CHAR got NUMBER error while trying to query the following:

case
    when PRODUCT_NAME = 'Something' and PRICE is not null 
    and QUANTITY > 0 
    then :P4_MY_NUMERIC_ITEM
    else 1
end MY_COLUMN

(item :p4 is a pre-filled (via computation) item. Value = 1 ) Funny (weird) thing is that it works when I actually select a number it works (but it's not what I need since the Item should be editable by the user):

case
    when PRODUCT_NAME = 'Something' and PRICE is not null 
    and QUANTITY > 0 
    then 2
    else 1
end MY_COLUMN

Does anybody know why this is happening and how to fix it keeping my item in the query?

Thanks!

Upvotes: 0

Views: 1418

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

Would this do?

else '1'

Because, Apex items are strings so :P4_MY_NUMERIC_ITEM (although containing numbers) is a string. CASE expects the same datatype, i.e.

case when ... then <string>
              else <expects a string as well, hence '1' and not just 1>
end

Alternatively, convert the :P4 item to a number:

case when ... then to_number(:P4_MY_NUMERIC_ITEM)
              else 1
end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

A case expression returns a single value, which is of a single type. All the branches should have the same type.

Despite its name :P4_MY_NUMERIC_ITEM appears to be a string. You have two choices:

  1. Use the appropriate type of :P4_MY_NUMERIC_ITEM.
  2. Or, change the else to return a string: else '1'.

Upvotes: 2

Related Questions