1、Oracel中的decode
DB2解决方案:用case条件表达式完成。
case两种语法模式:
(1)CASE
WHEN 条件 THEN 结果1
ELSE 结果2
END
(2)CASE 表达式1
WHEN 表达式2 THEN 结果1
ELSE 结果2
END
上面的WHEN可以重复多次,就像C中的SWITCH ..CASE的表达.
例如:
SELECT ORDNO,CUSNO,
CASE MONTH(SHIPDATE)
WHEN ''01'' THEN ''Jan''
WHEN ''02'' THEN ''Feb''
WHEN ''03'' THEN ''Mar''
WHEN ''04'' THEN ''Apr''
WHEN ''05'' THEN ''May''
WHEN ''06'' THEN ''Jun''
WHEN ''07'' THEN ''Jul''
WHEN ''08'' THEN ''Aug''
WHEN ''09'' THEN ''Sep''
WHEN ''10'' THEN ''Oct''
WHEN ''11'' THEN ''Nov''
WHEN ''12'' THEN ''Dec''
END
FROM FILE
应用实例:
Oracle SQL:
-------------------------
select decode(t.organtypecode, ''D'', t.parent, ''S'', t.parent, t.id)
from A_ORGAN t
where t.parent = 35
DB2 SQL:
-------------------------
select case x.organtypecode
when ''D'' then
x.parent
when ''S'' then
x.parent
else
x.id
end
from a_Organ x
where x.parent = 35;