SJR59 SJR59 - 1 year ago 143
SQL Question

Oracle DECODE not working

I have an oracle decode that is checking if a value is NULL before updating the decimal precision. The problem is when the value in the price_precision column isn't null the decode still goes to the d.price value, but it should go to the default value. Here is the line of code for the decode:

DECODE(d.PRICE_PRECISION, NULL, d.price,TO_CHAR(DECODE(d.price,NULL, '', d.price), CONCAT('9999990',RPAD('D', d.PRICE_PRECISION+1,'9')))) price

I know for a fact there is non-NULL data in the Price _Precision column, because I can see it in the return for the select statement. Is there something wrong with my decode? any ideas why the decode isn't going to the default statement?

Answer Source

It seems implicit conversion took place. Consider this

DECODE(d.PRICE_PRECISION, NULL,    to_char(d.price),   TO_CHAR....

From Oracle docs:

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download