10-344 同一行複数列中で最小上界か最大下界の値を取得

SQLパズル

getJyoukaiGekaiテーブル
L1  L2  L3  L4  L5  L6  L7  L8
--  --  --  --  --  --  --  --
 2   3   4   5   6   7   8   9
 5   6   7   8   9  11  13  15
 7   8   9  10  11  12  13  14
 9  10  13  14  15  16  17  18
11  22  27  28  29  30  31  32
12  13  14  15  16  17  18  19

下記の優先順位でExtValとして値を取得する。

優先順位1 L1,L2,L3,L4,L5,L6,L7,L8の中に12があれば取得
優先順位2 L1,L2,L3,L4,L5,L6,L7,L8の中に12より大きい値があれば、その中で最小値を取得
優先順位3 L1,L2,L3,L4,L5,L6,L7,L8の中の最大値を取得

出力結果
L1  L2  L3  L4  L5  L6  L7  L8  ExtVal
--  --  --  --  --  --  --  --  ------
 2   3   4   5   6   7   8   9       9
 5   6   7   8   9  11  13  15      13
 7   8   9  10  11  12  13  14      12
 9  10  13  14  15  16  17  18      13
11  22  27  28  29  30  31  32      22
12  13  14  15  16  17  18  19      12

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table getJyoukaiGekai(L1,L2,L3,L4,L5,L6,L7,L8) as
select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
select  5, 6, 7, 8, 9,11,13,15 from dual union all
select  7, 8, 9,10,11,12,13,14 from dual union all
select  9,10,13,14,15,16,17,18 from dual union all
select 11,22,27,28,29,30,31,32 from dual union all
select 12,13,14,15,16,17,18,19 from dual;

SQL

--■■■UnPivotを使わない方法■■■
select L1,L2,L3,L4,L5,L6,L7,L8,
case when 12 in(L1,L2,L3,L4,L5,L6,L7,L8) then 12
     when 12 > any(L1,L2,L3,L4,L5,L6,L7,L8)
     then Least(case when L1 < 12 then maxVal else L1 end,
                case when L2 < 12 then maxVal else L2 end,
                case when L3 < 12 then maxVal else L3 end,
                case when L4 < 12 then maxVal else L4 end,
                case when L5 < 12 then maxVal else L5 end,
                case when L6 < 12 then maxVal else L6 end,
                case when L7 < 12 then maxVal else L7 end,
                case when L8 < 12 then maxVal else L8 end)
     else maxVal end as ExtVal
from (select L1,L2,L3,L4,L5,L6,L7,L8,
      greatest(L1,L2,L3,L4,L5,L6,L7,L8) as maxVal
      from getJyoukaiGekai)
order by L1,L2,L3,L4,L5,L6,L7,L8;

--■■■sys.odciNumberListでUnPivotする方法■■■
select L1,L2,L3,L4,L5,L6,L7,L8,
nvl(min(case when 12 <= column_value
             then column_value  end),
    max(column_value)) as ExtVal
  from getJyoukaiGekai,
       Table(sys.odciNumberList(L1,L2,L3,L4,L5,L6,L7,L8))
group by L1,L2,L3,L4,L5,L6,L7,L8
order by L1,L2,L3,L4,L5,L6,L7,L8;

解説

可変長の引数を持つ
in述語やany述語やLeast関数やgreatest関数を駆使すれば、
UnPivotを使わずに済みます。