对多列使用 NVL - Oracle SQL [英] Using NVL for multiple columns - Oracle SQL
问题描述
早上好,我亲爱的 sql 向导和巫师,
Good morning my beloved sql wizards and sorcerers,
我想替换 3 个表中的 3 列数据.目前我正在使用 NVL 函数,但仅限于两列.
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.
请参见下面的示例:
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| xxx@blah.com
基本上我想做的是,如果 case_char.value 与 lookup_value.descr 存在匹配,则显示它,如果没有,则如果存在与 case_char.value 和 char_value.char 的匹配,则显示它.
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.
我只是想从 char_value 表中返回issue"的描述,但对于email",我想从 lookup_value 表中返回 descrlong(都在同一个别名char_val"下).
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.
谢谢各位
推荐答案
您可以嵌套 NVL:
NVL(a, NVL(b, NVL(c, d))
但更好的是,使用 SQL 标准 COALESCE,它可以采用多个参数,也适用于非 Oracle 系统:
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)
这篇关于对多列使用 NVL - Oracle SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!