Bug with CASE statement in Oracle?

Question

I have the following piece of code:

            IF i.cd_ver IS NULL OR i.cd_ver = '0'
            THEN
--Set value of v_cd_ver
                v_cd_ver := CASE i.cd_ver
                                WHEN NULL
                                THEN '9'
                                WHEN '0'
                                THEN '10'
                            END;
            END if;

"i" is the record found in the cursor referenced to an external table. The value is NULL or blank in the external table:

SELECT cd_ver FROM table_xtl WHERE cd_id = '123';

..This returned one row blank.

HOWEVER, v_cd_ver did not get set to '9' as expected. My work-around was to use IF THEN statements instead and it works. Why is the CASE statement not working as expected??

UPDATE: When I tried instead the following it worked:

        v_cd_ver := CASE 
                        WHEN i.cd_ver IS NULL
                        THEN '9'
                        WHEN i.cd_ver = '0'
                        THEN '10'
                    END;

Is this a bug or there's some reason why the former did not work?

user5683823 · Accepted Answer · 2017-03-08 23:47:33Z

4

case i.cd_ver when NULL ... is logically equivalent to case when i.cd_ver = NULL ... You know full well that such a comparison results in UNKNOWN, not in TRUE, so the case expression falls through to the default value, which is NULL. (To test this for yourself, add else 'x' before end and run it again, you'll see.)

The right way to write it is case when i.cd_ver is null then '9' when i.cd_ver = '0' then '10' end.

answered Mar 8, 2017 at 23:47

user5683823

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2017-03-08 23:55:24Z

3

That form of 'simple' case expression compares the value - i.cd_ver here - with each of the comparison expressions, looking for one that is equal.

In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr ...

The important bit there is "for which expr is equal to ...". Since nothing is ever equal to null, even itself, there is no match.

To look for nulls you would need a searched case exprssion instead:

            v_cd_ver := CASE
                            WHEN i.cd_ver IS NULL THEN '9'
                            WHEN i.cd_ver = '0' THEN '10'
                        END;

Not related, but are you sure 0, 9 and 10 should really be treated as strings?

edited Mar 8, 2017 at 23:55

answered Mar 8, 2017 at 23:50

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

6 Comments

user3224907 Over a year ago

Yes strings. Is there any difference in using CASE vs. IF THEN in this specific case? Performance?

Alex Poole Over a year ago

I wouldn't have thought so, case is more compact but you'd be doing the same logical comparisons and evaluations. I don't think I've ever done a comprehensive test, but it would be fairly simple to compare them if you think there could be a significanti mpact.

user5683823 Over a year ago

One benefit of CASE expressions is that the code can be more easily "copied over" to SQL code. We don't know how the OP is using this, but it is entirely possible that he doesn't need PL/SQL in the first place; if the code is to be re-written in plain SQL, CASE expressions can be copied and pasted; IF-THEN-ELSIF-THEN-...-END IF will have to be rewritten.

Alex Poole Over a year ago

Sure, but I'd only read/interpreted the comment as being about performance; I missed it was looking for other benefits...

user5683823 Over a year ago

Fair enough... but the greatest performance improvement may indeed come from rewriting whatever the PL/SQL code does in plain SQL! :-)

|

NikNik · Accepted Answer · 2017-03-08 23:46:05Z

0

Try to check empty ('') value or is NULL

edited Mar 8, 2017 at 23:46

answered Mar 8, 2017 at 23:44

NikNik

2,3012 gold badges18 silver badges36 bronze badges

1 Comment

user5683823 Over a year ago

@user3224907 - in Oracle '' is the same as NULL. (Not compliant with the SQL Standard... in their defense, the Oracle thing about '' and NULL was introduced before there was a Standard.)

Collectives™ on Stack Overflow

Bug with CASE statement in Oracle?

3 Answers 3

Comments

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related