oracle 注意点汇总

来源：互联网发布：图像形状特征提取算法编辑：程序博客网时间：2024/05/16 11:27

一、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'

0 0