create table tmptest
(
a int,
b int,
c int,
d int
)
begin
insert into tmptest values(1, 0, 0, 0);
insert into tmptest values(0, 7, 0, 0);
insert into tmptest values(0, 12, 0, 0);
insert into tmptest values(0, 30, 0, 0);
insert into tmptest values(0, 0, 9, 0);
insert into tmptest values(0, 0, 10, 0);
insert into tmptest values(0, 0, 12, 0);
insert into tmptest values(0, 0, 21, 0);
insert into tmptest values(0, 0, 0, 25);
insert into tmptest values(0, 0, 0, 30);
end;
select a, b, c, d from tmptest
select max(a) a, max(b) b, max(c) c, max(d) d
from
(
select a, b, c, d, row_number() over(order by gubn) as whoa
from
(
SELECT A, B, C, D,
case A_2 when 1 then 1 else
case B_2 when 1 then 2 else
case C_2 when 1 then 3 else
case d_2 when 1 then 4 end
end
end
end as gubn
--DECODE(A_2, 1, 1, DECODE(B_2, 1, 2, DECODE(C_2, 1, 3, DECODE(D_2, 1, 4)))) GUBN
-- 오라클 디코드의 한줄 압박 ㅎㅎ. 점점 case가 무식해 보인다. 제길슨
FROM
( SELECT A, B, C, D
-- , DECODE(A, 0, NULL, 1) A_2
-- , DECODE(B, 0, NULL, 1) B_2
-- , DECODE(C, 0, NULL, 1) C_2
-- , DECODE(D, 0, NULL, 1) D_2
, case a when 0 then null else 1 end a_2
, case b when 0 then null else 1 end b_2
, case c when 0 then null else 1 end c_2
, case d when 0 then null else 1 end d_2
FROM TMPTEST)a
)a
)b
group by whoa