我有以下 SQL 语句:
select
DOCUMENT.DOCUMENT_ID,
(case
when DOCUMENT.CLASSIFICATION_CODE is not null
then DOCUMENT.CLASSIFICATION_CODE
else TEMPLATE.CLASSIFICATION_CODE end) CLASSIFICATION_CODE,
CLASSIFICATION.NAME CLASSIFICATION_NAME
from
DOCUMENT,
TEMPLATE,
CLASSIFICATION
where
DOCUMENT.TEMPLATE_ID = TEMPLATE.TEMPLATE_ID and
DOCUMENT.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+)
我想做的是:
if DOCUMENT.CLASSIFICATION_CODE IS NOT NULL THEN
DOCUMENT.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+),
ELSE
TEMPLATE.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+)
这可能吗?
您可以使用NVL http://www.oradev.com/nvl.html或者更标准的COALESCE https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm为了那个原因:
COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE)
= CLASSIFICATION.CLASSIFICATION_CODE(+)
在 ANSI 语法中:
select
DOCUMENT.DOCUMENT_ID,
COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE)
CLASSIFICATION_CODE,
CLASSIFICATION.NAME CLASSIFICATION_NAME
from
DOCUMENT
inner join
TEMPLATE on DOCUMENT.TEMPLATE_ID = TEMPLATE.TEMPLATE_ID
left join
CLASSIFICATION on
COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE)
= CLASSIFICATION.CLASSIFICATION_CODE
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)