Using NVL for multiple columns - Oracle SQL

Question

Good morning my beloved sql wizards and sorcerers,

I am wanting to substitute on 3 columns of data across 3 tables. Currently I am using the NVL function, however that is restricted to two columns.

See below for an example:

    SELECT ccc.case_id,
           NVL (ccvl.descr, ccc.char)) char_val

               FROM case_char ccc, char_value ccvl, lookup_value lval1
              WHERE   
                    ccvl.descr(+) = ccc.value
                    AND ccc.value = lval1.descr (+)
                    AND ccc.case_id IN ('123'))



     case_char table
     case_id|char |value
       123  |email| work_email
       124  |issue| tim_ 



     char_value table
     char  | descr
work_email | complaint mail
    tim_   | timeliness


    lookup_value table
    descr  | descrlong
 work_email| [email protected]

Essentially what I am trying to do is if there exists a match for case_char.value with lookup_value.descr then display it, if not, then if there exists a match with case_char.value and char_value.char then display it.

I am just trying to return the description for 'issue'from the char_value table, but for 'email' I want to return the descrlong from the lookup_value table (all under the same alias 'char_val').

So my question is, how do I achieve this keeping in mind that I want them to appear under the same alias.

Let me know if you require any further information.

Thanks guys

Mcinerney

Answer 1

You could nest NVL:

 NVL(a, NVL(b, NVL(c, d))

But even better, use the SQL-standard COALESCE, which does take multiple arguments and also works on non-Oracle systems:

COALESCE(a, b, c, d)

Cad

Thanks for this. I used a nested NVL and it was perfect for what I needed. – Mcinerney

Answer 2

How about using COALESCE:

COALESCE(ccvl.descr, ccc.char)

Kiyohara

Answer 3

Better to Use COALESCE(a, b, c, d) because of below reason:

• Nested NVL logic can be achieved in single COALESCE(a, b, c, d).

• It is SQL standard to use COALESCE.

• COALESCE gives better performance in terms, NVL always first calculate both of the queries used and then compare if the first value is null then return a second value. but in COALESCE function it checks one by one and returns response whenever if found a non-null value instead of executing all the used queries.

Lindemann