Convert decode function to Case statement give wrong results

Toad for Oracle
1

Hi I tested the Refactor function where you can let TOAD (12.5.1.1 for Oracle) convert decode-statements into CASE statements.

Luckily I did some testing before production and I found errors in the converted code.

Try this:

select decode(:var,null,‘Nothing’,‘Value’) from dual

If :var is null, the the result should be ‘Nothing’

TOAD converted this into : SELECT CASE WHEN :var = NULL THEN ‘nothing’ ELSE ‘Value’ END FROM DUAL;

The clause “WHEN :var = NULL” will always return ‘Value’ even if :var is null. The correct syntax should be “WHEN :var is NULL”.

Pls fix…

Jens

2

Thanks. It’s too late for 12.6, but I’ll fix this when we start up the next beta cycle.

3

In Oracle, assignment to NULL using “=” is not advised. Instead, change the code as follows:

SELECT CASE WHEN :var IS NULL THEN ‘nothing’ ELSE ‘Value’ END FROM DUAL;

Assigning NULL to a variable using an equal sign will always be false. That is why your answer is always defaulting to “Value”. Hope this helps.