我有以下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(+)
这可能吗?
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句