7-16 古のSQL(条件分岐その2)

SQLパズル

以下の検索case式を
Oracle8のSQLで記述する。

select ColA,ColB,
case when ColA = 1 then 'a'
     when ColB = 3 then 'b'
     when ColB = 5 then 'c'
     else 'd' end as "case式"
from (select 1 as ColA,3 as ColB from dual
union all select 0,3 from dual
union all select 0,5 from dual
union all select 0,6 from dual);

SQL

select ColA,ColB,
case when ColA = 1 then 'a'
     when ColB = 3 then 'b'
     when ColB = 5 then 'c'
     else 'd' end as "case式",
decode(ColA,1,'a',decode(ColB,3,'b',5,'c','d')) as "decode関数"
from (select 1 as ColA,3 as ColB from dual
union all select 0,3 from dual
union all select 0,5 from dual
union all select 0,6 from dual);

解説

case式を使わずに条件分岐を行う、古のSQLです。
分かりづらいです。

---