Oracle decode语句

Question

我有一个查询，对于ln_prd_typ返回null。我想用NA替换null，我该怎么做呢？下面是我的问题

select '211' as "Asset_id",
(Select decode( lv12.ATTRIBUTE4, '', 'NA', lv12.ATTRIBUTE4 )
                    FROM XXFMSLS. XXFM_FAH_CUSTOM_LVL2_RULES lv12
                    WHERE lv12.LEVEL2_RULE_KEY = lvl1.LEVEL2_RULE_KEY  ) as "LN_PRD_TYP"
from xxfmsls.XXFM_FAH_CUSTOM_LVL1_RULES lvl1

Answer 1

如果要在子查询中执行此操作，请使用coalesce

select '211' as "Asset_id",
       (Select coalesce( lv12.ATTRIBUTE4, 'NA')
        FROM XXFMSLS. XXFM_FAH_CUSTOM_LVL2_RULES lv12
        WHERE lv12.LEVEL2_RULE_KEY = lvl1.LEVEL2_RULE_KEY
       ) as "LN_PRD_TYP"
from xxfmsls.XXFM_FAH_CUSTOM_LVL1_RULES lvl1;

如果问题是您仍然获得NULL值，那是因为子查询不匹配--因此内部select中的逻辑永远不会被执行。我建议使用left join：

select '211' as "Asset_id",
       coalesce(lv12.ATTRIBUTE4, 'NA') as "LN_PRD_TYP"
from xxfmsls.XXFM_FAH_CUSTOM_LVL1_RULES lvl1 left join
     XXFMSLS.XXFM_FAH_CUSTOM_LVL2_RULES lv12
     on lv12.LEVEL2_RULE_KEY = lvl1.LEVEL2_RULE_KEY;