oracle 注意点汇总

一、case when 与 decode

1 错误写法

SELECT
            sinfo.linkedSup,
            sinfo.ID,
            if (
            sinfo.supplierNature='个人' 
            and sinfo.linkedSup is not null 
            ) 
            then sinfo.name||'('||sinfo.linkedSup||')' 
            else sinfo.name  
        end if as name,
        sinfo.ENTNO,
        sinfo.supplierNo,
        sinfo.MAINBUSINESS,
        sinfo.supplieridentification supplierLevelName 
    FROM
        supplierinfo sinfo 

        where
            sinfo.entNo='30000238'

2 正确写法一

SELECT
            sinfo.linkedSup,
            sinfo.ID,
            case when (
            sinfo.supplierNature='个人' 
            and sinfo.linkedSup is not null 
            ) 
            then sinfo.name||'('||sinfo.linkedSup||')' 
            else sinfo.name  
        end as name,
        sinfo.ENTNO,
        sinfo.supplierNo,
        sinfo.MAINBUSINESS,
        sinfo.supplieridentification supplierLevelName 
    FROM
        supplierinfo sinfo 

        where
            sinfo.entNo='30000238'

3 正确写法二

SELECT
            sinfo.linkedSup,
            sinfo.ID,
            decode(sinfo.supplierNature,'个人',(decode(sinfo.linkedSup,null,sinfo.name,sinfo.name||'('||sinfo.linkedSup||')')),sinfo.name) as name,
        sinfo.ENTNO,
        sinfo.supplierNo,
        sinfo.MAINBUSINESS,
        sinfo.supplieridentification supplierLevelName 
    FROM
        supplierinfo sinfo 

        where
            sinfo.entNo='30000238'