Using NVL for multiple columns - Oracle SQL
Solution 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)
Solution 2
How about using COALESCE:
COALESCE(ccvl.descr, ccc.char)
Heisenberg
Updated on June 06, 2020Comments
-
Heisenberg about 4 years
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
-
Heisenberg almost 10 yearsThanks for this. I used a nested NVL and it was perfect for what I needed.