SQL case statement: Return specified string if null else return select value

Question

Below is an excerpt from my sql code, I am trying to return a string 'Off-Campus' if the value from select statement is null. If the value is not null, I want to return the value itself. However, I am getting an error:ORA-00904: "SITE_DESC": invalid identifier 00904. 00000 - "%s: invalid identifier"

Please advice on how I can go about this.

WHEN 'S'   THEN (
            case when(SELECT
                site_desc
            FROM
                building
            WHERE
                site_code = (
                    SELECT
                        code
                    FROM
                        table2
                    WHERE
                            name = 'code'
                )
        ) is null then 'Off-Campus' else site_desc end
        )
WHEN 'F'   THEN (
....   ......     .......
)

you have a couple of tools avialable for what you want ... exists and coalesce functions are some exemples — Minus
– Minus, Commented Feb 6, 2018 at 21:26

dnoeth · Accepted Answer · 2018-02-06 20:38:30Z

2

Try COALESCE:

WHEN 'S'
THEN
        COALESCE(SELECT
            site_desc
        FROM
            building
        WHERE
            site_code = (
                SELECT
                    code
                FROM
                    table2
                WHERE
                        name = 'code'
            ),'Off-Campus')

answered Feb 6, 2018 at 20:38

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

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

4 Comments

Andrew Over a year ago

I am no oracle expert but wouldn't WHERE site_code = (SELECT code FROM... have an issue due to multiple possible values in the return? Shouldn't it SELECT TOP 1 code... to guarantee a scalar result or WHERE site_code IN (SELECT code... to handle multiple return values?

dnoeth Over a year ago

Of course multiple matching rows will cause an error. But this is probably based on the Primary Key anyway, otherwise you have to decide which row to return. Or you switch to an EXISTS instead.

jimguire Over a year ago

Thank you, that worked perfectly. I did not know about COALESCE

Andrew Over a year ago

@jimguire This probably won't work for you, based on how your select looks here (though that could be due to sanitizing for us) If name in table2 is guaranteed unique, you are alright, if not then you have a problem where multiple codes are returned. See my answer.

Andrew · Accepted Answer · 2018-02-06 21:09:39Z

You can use COALESCE:

WHEN 'S' THEN (
    COALESCE(SELECT site_desc
        FROM building
        WHERE site_code IN (SELECT code
            FROM table2
            WHERE name = 'code'), 
        'Off-Campus')

Or:

WHEN 'S' THEN (
    COALESCE(SELECT site_desc
        FROM building
        WHERE site_code = (SELECT TOP 1 code
            FROM table2
            WHERE name = 'code'), 
        'Off-Campus')

The difference between these two is how you handle the innermost subquery, since it's in your where clause you can either test that your value site_code is IN the results, or guarantee one result by only taking the TOP 1 and testing for equality. Doing either is good practice even if you can guarantee only one name = 'code' in table2. The first option is likely to be more useful, as it will return all codes for name='code' and test if site_code is in the results.

COALESCE(expression [,... expressionN]) goes through the expressions passed in from left to right, evaluating each until it has none left to evaluate or finds one that returns a value other than null. You can pass any number of arguments to it and it will return the first one that is not null. In your case the second expression is a hard coded string and the first is a subselect which may return null.

Gordon Linoff · Accepted Answer · 2018-02-07 02:54:12Z

0

You could write this using coalesce():

WHEN 'S' 
THEN coalesce( (SELECT site_desc
                FROM building
                WHERE site_code = (SELECT code FROM table2 WHERE name = 'code')
               ), 'Off-Campus'
             )

I can't say I'm a fan of the nested subquery, so there might be a simpler way to express the logic.

edited Feb 7, 2018 at 2:54

answered Feb 6, 2018 at 20:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Andrew Over a year ago

Joining could get around the nested subqueries but that would need more of the actual query to write...

Collectives™ on Stack Overflow

SQL case statement: Return specified string if null else return select value

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related