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

Question

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!

Answer 1

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