|
고수님들의 의견을 좀 들어보려고 합니다.
하단의 sql이 좀 어렵고 복잡합니다.
문제는 case 와 decode 인데요....버전 11g 에서는 case 나 decde 의 갯수에 상관없이 sql이 잘 돌아가는데, 버전 9i에서 돌릴려니
case, decde 가 127개 밖에 인식이 안 된다고 하네요.
아래 sql 에서 9i 에서 돌아갈수 있도록 decde 나 case 대신 쓸 수 있는 것이 있을까요??
SELECT
'2012' as base_yy, -- 지원년도
'2012' as fin_yy, -- 재무년도
supt_fld_cd, -- 지원구분코드 (A: 자금지원, B: 보증....)
'A' as supt_cd_01, -- 지원구분상세01 (A: 매출기준, B: 종업원수기준, C: 업력....)
case
when mod(LV,8) = 1 then 'A'
when mod(LV,8) = 2 then 'B'
when mod(LV,8) = 3 then 'C'
when mod(LV,8) = 4 then 'D'
when mod(LV,8) = 5 then 'E'
when mod(LV,8) = 6 then 'F'
when mod(LV,8) = 7 then 'G'
when mod(LV,8) = 0 then 'H'
else ''
end supt_cd_02, -- 지원구분상세02 (A: 5인미만, B: 5~10인미만....)
'D' as inde_fld_cd, -- 지표구분코드 (A: 성장성, B: 수익성, C: 활동성....)
'A' as inde_cd_01, -- 지표구분상세01 (지표구분코드에 따라 달라짐)
case
when LV <= 8 then 'C7001260000'
when LV > 8 and LV <= 16 then 'C7001227900'
when LV > 16 and LV <= 24 then 'C7001228000'
when LV > 24 and LV <= 32 then 'C7001228100'
when LV > 32 and LV <= 40 then 'C7001530400'
when LV > 40 and LV <= 48 then 'C7001223700'
when LV > 48 and LV <= 56 then 'C7001224300'
when LV > 56 and LV <= 64 then 'C7001224400'
when LV > 64 and LV <= 72 then 'C7001224500'
when LV > 72 and LV <= 80 then 'C7001520000'
when LV > 80 and LV <= 88 then 'C7001530100'
when LV > 88 and LV <= 96 then 'C7001225200'
when LV > 96 and LV <= 104 then 'C7001530500'
when LV > 104 and LV <= 112 then 'C7001225300'
when LV > 112 and LV <= 120 then 'C7001530600'
when LV > 120 and LV <= 128 then 'C7001240100'
when LV > 128 and LV <= 136 then 'C7001240200'
when LV > 136 and LV <= 144 then 'C7001250100'
when LV > 144 and LV <= 152 then 'C7001532300'
when LV > 152 then 'C7001110000'
else ''
end acc_cd_full,
DECODE(LV, 1, TOT_C7001260000_VAL01, 2, TOT_C7001260000_VAL02, 3, TOT_C7001260000_VAL03, 4, TOT_C7001260000_VAL04, 5, TOT_C7001260000_VAL05, 6, TOT_C7001260000_VAL06, 7, TOT_C7001260000_VAL07, 8, TOT_C7001260000_VAL08,
9, TOT_C7001227900_VAL01, 10, TOT_C7001227900_VAL02, 11, TOT_C7001227900_VAL03, 12, TOT_C7001227900_VAL04, 13, TOT_C7001227900_VAL05, 14, TOT_C7001227900_VAL06, 15, TOT_C7001227900_VAL07, 16, TOT_C7001227900_VAL08,
17, TOT_C7001228000_VAL01, 18, TOT_C7001228000_VAL02, 19, TOT_C7001228000_VAL03, 20, TOT_C7001228000_VAL04, 21, TOT_C7001228000_VAL05, 22, TOT_C7001228000_VAL06, 23, TOT_C7001228000_VAL07, 24, TOT_C7001228000_VAL08,
25, TOT_C7001228100_VAL01, 26, TOT_C7001228100_VAL02, 27, TOT_C7001228100_VAL03, 28, TOT_C7001228100_VAL04, 29, TOT_C7001228100_VAL05, 30, TOT_C7001228100_VAL06, 31, TOT_C7001228100_VAL07, 32, TOT_C7001228100_VAL08,
33, TOT_C7001530400_VAL01, 34, TOT_C7001530400_VAL02, 35, TOT_C7001530400_VAL03, 36, TOT_C7001530400_VAL04, 37, TOT_C7001530400_VAL05, 38, TOT_C7001530400_VAL06, 39, TOT_C7001530400_VAL07, 40, TOT_C7001530400_VAL08,
41, TOT_C7001223700_VAL01, 42, TOT_C7001223700_VAL02, 43, TOT_C7001223700_VAL03, 44, TOT_C7001223700_VAL04, 45, TOT_C7001223700_VAL05, 46, TOT_C7001223700_VAL06, 47, TOT_C7001223700_VAL07, 48, TOT_C7001223700_VAL08,
49, TOT_C7001224300_VAL01, 50, TOT_C7001224300_VAL02, 51, TOT_C7001224300_VAL03, 52, TOT_C7001224300_VAL04, 53, TOT_C7001224300_VAL05, 54, TOT_C7001224300_VAL06, 55, TOT_C7001224300_VAL07, 56, TOT_C7001224300_VAL08,
57, TOT_C7001224400_VAL01, 58, TOT_C7001224400_VAL02, 59, TOT_C7001224400_VAL03, 60, TOT_C7001224400_VAL04, 61, TOT_C7001224400_VAL05, 62, TOT_C7001224400_VAL06, 63, TOT_C7001224400_VAL07, 64, TOT_C7001224400_VAL08,
65, TOT_C7001224500_VAL01, 66, TOT_C7001224500_VAL02, 67, TOT_C7001224500_VAL03, 68, TOT_C7001224500_VAL04, 69, TOT_C7001224500_VAL05, 70, TOT_C7001224500_VAL06, 71, TOT_C7001224500_VAL07, 72, TOT_C7001224500_VAL08,
73, TOT_C7001520000_VAL01, 74, TOT_C7001520000_VAL02, 75, TOT_C7001520000_VAL03, 76, TOT_C7001520000_VAL04, 77, TOT_C7001520000_VAL05, 78, TOT_C7001520000_VAL06, 79, TOT_C7001520000_VAL07, 80, TOT_C7001520000_VAL08,
81, TOT_C7001530100_VAL01, 82, TOT_C7001530100_VAL02, 83, TOT_C7001530100_VAL03, 84, TOT_C7001530100_VAL04, 85, TOT_C7001530100_VAL05, 86, TOT_C7001530100_VAL06, 87, TOT_C7001530100_VAL07, 88, TOT_C7001530100_VAL08,
89, TOT_C7001225200_VAL01, 90, TOT_C7001225200_VAL02, 91, TOT_C7001225200_VAL03, 92, TOT_C7001225200_VAL04, 93, TOT_C7001225200_VAL05, 94, TOT_C7001225200_VAL06, 95, TOT_C7001225200_VAL07, 96, TOT_C7001225200_VAL08,
97, TOT_C7001530500_VAL01, 98, TOT_C7001530500_VAL02, 99, TOT_C7001530500_VAL03,100, TOT_C7001530500_VAL04,101, TOT_C7001530500_VAL05,102, TOT_C7001530500_VAL06,103, TOT_C7001530500_VAL07,104, TOT_C7001530500_VAL08,
105, TOT_C7001225300_VAL01,106, TOT_C7001225300_VAL02,107, TOT_C7001225300_VAL03,108, TOT_C7001225300_VAL04,109, TOT_C7001225300_VAL05,110, TOT_C7001225300_VAL06,111, TOT_C7001225300_VAL07,112, TOT_C7001225300_VAL08,
113, TOT_C7001530600_VAL01,114, TOT_C7001530600_VAL02,115, TOT_C7001530600_VAL03,116, TOT_C7001530600_VAL04,117, TOT_C7001530600_VAL05,118, TOT_C7001530600_VAL06,119, TOT_C7001530600_VAL07,120, TOT_C7001530600_VAL08,
121, TOT_C7001240100_VAL01,122, TOT_C7001240100_VAL02,123, TOT_C7001240100_VAL03,124, TOT_C7001240100_VAL04,125, TOT_C7001240100_VAL05,126, TOT_C7001240100_VAL06,127, TOT_C7001240100_VAL07,128, TOT_C7001240100_VAL08,
129, TOT_C7001240200_VAL01,130, TOT_C7001240200_VAL02,131, TOT_C7001240200_VAL03,132, TOT_C7001240200_VAL04,133, TOT_C7001240200_VAL05,134, TOT_C7001240200_VAL06,135, TOT_C7001240200_VAL07,136, TOT_C7001240200_VAL08,
137, TOT_C7001250100_VAL01,138, TOT_C7001250100_VAL02,139, TOT_C7001250100_VAL03,140, TOT_C7001250100_VAL04,141, TOT_C7001250100_VAL05,142, TOT_C7001250100_VAL06,143, TOT_C7001250100_VAL07,144, TOT_C7001250100_VAL08,
145, TOT_C7001532300_VAL01,146, TOT_C7001532300_VAL02,147, TOT_C7001532300_VAL03,148, TOT_C7001532300_VAL04,149, TOT_C7001532300_VAL05,150, TOT_C7001532300_VAL06,151, TOT_C7001532300_VAL07,152, TOT_C7001532300_VAL08,
153, TOT_C7001110000_VAL01,154, TOT_C7001110000_VAL02,155, TOT_C7001110000_VAL03,156, TOT_C7001110000_VAL04,157, TOT_C7001110000_VAL05,158, TOT_C7001110000_VAL06,159, TOT_C7001110000_VAL07,160, TOT_C7001110000_VAL08) SUM_VAL,
sysdate as UPD_DT,
'admin' as CEMNO
FROM (
select
supt_fld_cd,
sum(C7001260000_VAL01) TOT_C7001260000_VAL01,
sum(C7001260000_VAL02) TOT_C7001260000_VAL02,
sum(C7001260000_VAL03) TOT_C7001260000_VAL03,
sum(C7001260000_VAL04) TOT_C7001260000_VAL04,
sum(C7001260000_VAL05) TOT_C7001260000_VAL05,
sum(C7001260000_VAL06) TOT_C7001260000_VAL06,
sum(C7001260000_VAL07) TOT_C7001260000_VAL07,
sum(C7001260000_VAL08) TOT_C7001260000_VAL08,
sum(C7001227900_VAL01) TOT_C7001227900_VAL01,
sum(C7001227900_VAL02) TOT_C7001227900_VAL02,
sum(C7001227900_VAL03) TOT_C7001227900_VAL03,
sum(C7001227900_VAL04) TOT_C7001227900_VAL04,
sum(C7001227900_VAL05) TOT_C7001227900_VAL05,
sum(C7001227900_VAL06) TOT_C7001227900_VAL06,
sum(C7001227900_VAL07) TOT_C7001227900_VAL07,
sum(C7001227900_VAL08) TOT_C7001227900_VAL08,
sum(C7001228000_VAL01) TOT_C7001228000_VAL01,
sum(C7001228000_VAL02) TOT_C7001228000_VAL02,
sum(C7001228000_VAL03) TOT_C7001228000_VAL03,
sum(C7001228000_VAL04) TOT_C7001228000_VAL04,
sum(C7001228000_VAL05) TOT_C7001228000_VAL05,
sum(C7001228000_VAL06) TOT_C7001228000_VAL06,
sum(C7001228000_VAL07) TOT_C7001228000_VAL07,
sum(C7001228000_VAL08) TOT_C7001228000_VAL08,
sum(C7001228100_VAL01) TOT_C7001228100_VAL01,
sum(C7001228100_VAL02) TOT_C7001228100_VAL02,
sum(C7001228100_VAL03) TOT_C7001228100_VAL03,
sum(C7001228100_VAL04) TOT_C7001228100_VAL04,
sum(C7001228100_VAL05) TOT_C7001228100_VAL05,
sum(C7001228100_VAL06) TOT_C7001228100_VAL06,
sum(C7001228100_VAL07) TOT_C7001228100_VAL07,
sum(C7001228100_VAL08) TOT_C7001228100_VAL08,
sum(C7001530400_VAL01) TOT_C7001530400_VAL01,
sum(C7001530400_VAL02) TOT_C7001530400_VAL02,
sum(C7001530400_VAL03) TOT_C7001530400_VAL03,
sum(C7001530400_VAL04) TOT_C7001530400_VAL04,
sum(C7001530400_VAL05) TOT_C7001530400_VAL05,
sum(C7001530400_VAL06) TOT_C7001530400_VAL06,
sum(C7001530400_VAL07) TOT_C7001530400_VAL07,
sum(C7001530400_VAL08) TOT_C7001530400_VAL08,
sum(C7001223700_VAL01) TOT_C7001223700_VAL01,
sum(C7001223700_VAL02) TOT_C7001223700_VAL02,
sum(C7001223700_VAL03) TOT_C7001223700_VAL03,
sum(C7001223700_VAL04) TOT_C7001223700_VAL04,
sum(C7001223700_VAL05) TOT_C7001223700_VAL05,
sum(C7001223700_VAL06) TOT_C7001223700_VAL06,
sum(C7001223700_VAL07) TOT_C7001223700_VAL07,
sum(C7001223700_VAL08) TOT_C7001223700_VAL08,
sum(C7001224300_VAL01) TOT_C7001224300_VAL01,
sum(C7001224300_VAL02) TOT_C7001224300_VAL02,
sum(C7001224300_VAL03) TOT_C7001224300_VAL03,
sum(C7001224300_VAL04) TOT_C7001224300_VAL04,
sum(C7001224300_VAL05) TOT_C7001224300_VAL05,
sum(C7001224300_VAL06) TOT_C7001224300_VAL06,
sum(C7001224300_VAL07) TOT_C7001224300_VAL07,
sum(C7001224300_VAL08) TOT_C7001224300_VAL08,
sum(C7001224400_VAL01) TOT_C7001224400_VAL01,
sum(C7001224400_VAL02) TOT_C7001224400_VAL02,
sum(C7001224400_VAL03) TOT_C7001224400_VAL03,
sum(C7001224400_VAL04) TOT_C7001224400_VAL04,
sum(C7001224400_VAL05) TOT_C7001224400_VAL05,
sum(C7001224400_VAL06) TOT_C7001224400_VAL06,
sum(C7001224400_VAL07) TOT_C7001224400_VAL07,
sum(C7001224400_VAL08) TOT_C7001224400_VAL08,
sum(C7001224500_VAL01) TOT_C7001224500_VAL01,
sum(C7001224500_VAL02) TOT_C7001224500_VAL02,
sum(C7001224500_VAL03) TOT_C7001224500_VAL03,
sum(C7001224500_VAL04) TOT_C7001224500_VAL04,
sum(C7001224500_VAL05) TOT_C7001224500_VAL05,
sum(C7001224500_VAL06) TOT_C7001224500_VAL06,
sum(C7001224500_VAL07) TOT_C7001224500_VAL07,
sum(C7001224500_VAL08) TOT_C7001224500_VAL08,
sum(C7001520000_VAL01) TOT_C7001520000_VAL01,
sum(C7001520000_VAL02) TOT_C7001520000_VAL02,
sum(C7001520000_VAL03) TOT_C7001520000_VAL03,
sum(C7001520000_VAL04) TOT_C7001520000_VAL04,
sum(C7001520000_VAL05) TOT_C7001520000_VAL05,
sum(C7001520000_VAL06) TOT_C7001520000_VAL06,
sum(C7001520000_VAL07) TOT_C7001520000_VAL07,
sum(C7001520000_VAL08) TOT_C7001520000_VAL08,
sum(C7001530100_VAL01) TOT_C7001530100_VAL01,
sum(C7001530100_VAL02) TOT_C7001530100_VAL02,
sum(C7001530100_VAL03) TOT_C7001530100_VAL03,
sum(C7001530100_VAL04) TOT_C7001530100_VAL04,
sum(C7001530100_VAL05) TOT_C7001530100_VAL05,
sum(C7001530100_VAL06) TOT_C7001530100_VAL06,
sum(C7001530100_VAL07) TOT_C7001530100_VAL07,
sum(C7001530100_VAL08) TOT_C7001530100_VAL08,
sum(C7001225200_VAL01) TOT_C7001225200_VAL01,
sum(C7001225200_VAL02) TOT_C7001225200_VAL02,
sum(C7001225200_VAL03) TOT_C7001225200_VAL03,
sum(C7001225200_VAL04) TOT_C7001225200_VAL04,
sum(C7001225200_VAL05) TOT_C7001225200_VAL05,
sum(C7001225200_VAL06) TOT_C7001225200_VAL06,
sum(C7001225200_VAL07) TOT_C7001225200_VAL07,
sum(C7001225200_VAL08) TOT_C7001225200_VAL08,
sum(C7001530500_VAL01) TOT_C7001530500_VAL01,
sum(C7001530500_VAL02) TOT_C7001530500_VAL02,
sum(C7001530500_VAL03) TOT_C7001530500_VAL03,
sum(C7001530500_VAL04) TOT_C7001530500_VAL04,
sum(C7001530500_VAL05) TOT_C7001530500_VAL05,
sum(C7001530500_VAL06) TOT_C7001530500_VAL06,
sum(C7001530500_VAL07) TOT_C7001530500_VAL07,
sum(C7001530500_VAL08) TOT_C7001530500_VAL08,
sum(C7001225300_VAL01) TOT_C7001225300_VAL01,
sum(C7001225300_VAL02) TOT_C7001225300_VAL02,
sum(C7001225300_VAL03) TOT_C7001225300_VAL03,
sum(C7001225300_VAL04) TOT_C7001225300_VAL04,
sum(C7001225300_VAL05) TOT_C7001225300_VAL05,
sum(C7001225300_VAL06) TOT_C7001225300_VAL06,
sum(C7001225300_VAL07) TOT_C7001225300_VAL07,
sum(C7001225300_VAL08) TOT_C7001225300_VAL08,
sum(C7001530600_VAL01) TOT_C7001530600_VAL01,
sum(C7001530600_VAL02) TOT_C7001530600_VAL02,
sum(C7001530600_VAL03) TOT_C7001530600_VAL03,
sum(C7001530600_VAL04) TOT_C7001530600_VAL04,
sum(C7001530600_VAL05) TOT_C7001530600_VAL05,
sum(C7001530600_VAL06) TOT_C7001530600_VAL06,
sum(C7001530600_VAL07) TOT_C7001530600_VAL07,
sum(C7001530600_VAL08) TOT_C7001530600_VAL08,
sum(C7001240100_VAL01) TOT_C7001240100_VAL01,
sum(C7001240100_VAL02) TOT_C7001240100_VAL02,
sum(C7001240100_VAL03) TOT_C7001240100_VAL03,
sum(C7001240100_VAL04) TOT_C7001240100_VAL04,
sum(C7001240100_VAL05) TOT_C7001240100_VAL05,
sum(C7001240100_VAL06) TOT_C7001240100_VAL06,
sum(C7001240100_VAL07) TOT_C7001240100_VAL07,
sum(C7001240100_VAL08) TOT_C7001240100_VAL08,
sum(C7001240200_VAL01) TOT_C7001240200_VAL01,
sum(C7001240200_VAL02) TOT_C7001240200_VAL02,
sum(C7001240200_VAL03) TOT_C7001240200_VAL03,
sum(C7001240200_VAL04) TOT_C7001240200_VAL04,
sum(C7001240200_VAL05) TOT_C7001240200_VAL05,
sum(C7001240200_VAL06) TOT_C7001240200_VAL06,
sum(C7001240200_VAL07) TOT_C7001240200_VAL07,
sum(C7001240200_VAL08) TOT_C7001240200_VAL08,
sum(C7001250100_VAL01) TOT_C7001250100_VAL01,
sum(C7001250100_VAL02) TOT_C7001250100_VAL02,
sum(C7001250100_VAL03) TOT_C7001250100_VAL03,
sum(C7001250100_VAL04) TOT_C7001250100_VAL04,
sum(C7001250100_VAL05) TOT_C7001250100_VAL05,
sum(C7001250100_VAL06) TOT_C7001250100_VAL06,
sum(C7001250100_VAL07) TOT_C7001250100_VAL07,
sum(C7001250100_VAL08) TOT_C7001250100_VAL08,
sum(C7001532300_VAL01) TOT_C7001532300_VAL01,
sum(C7001532300_VAL02) TOT_C7001532300_VAL02,
sum(C7001532300_VAL03) TOT_C7001532300_VAL03,
sum(C7001532300_VAL04) TOT_C7001532300_VAL04,
sum(C7001532300_VAL05) TOT_C7001532300_VAL05,
sum(C7001532300_VAL06) TOT_C7001532300_VAL06,
sum(C7001532300_VAL07) TOT_C7001532300_VAL07,
sum(C7001532300_VAL08) TOT_C7001532300_VAL08,
sum(C7001110000_VAL01) TOT_C7001110000_VAL01,
sum(C7001110000_VAL02) TOT_C7001110000_VAL02,
sum(C7001110000_VAL03) TOT_C7001110000_VAL03,
sum(C7001110000_VAL04) TOT_C7001110000_VAL04,
sum(C7001110000_VAL05) TOT_C7001110000_VAL05,
sum(C7001110000_VAL06) TOT_C7001110000_VAL06,
sum(C7001110000_VAL07) TOT_C7001110000_VAL07,
sum(C7001110000_VAL08) TOT_C7001110000_VAL08
from (
select
kedcd,
bzno,
supt_fld_cd,
val,
case when (val < 1000000000) then 1 else 0 end as com_cnt01,
case when (val >= 1000000000 and val < 3000000000) then 1 else 0 end as com_cnt02,
case when (val >= 3000000000 and val < 5000000000) then 1 else 0 end as com_cnt03,
case when (val >= 5000000000 and val < 10000000000) then 1 else 0 end as com_cnt04,
case when (val >= 10000000000 and val < 20000000000) then 1 else 0 end as com_cnt05,
case when (val >= 20000000000 and val < 30000000000) then 1 else 0 end as com_cnt06,
case when (val >= 30000000000 and val < 50000000000) then 1 else 0 end as com_cnt07,
case when (val >= 50000000000) then 1 else 0 end as com_cnt08,
case when (val < 1000000000) then C7001260000_VAL else 0 end as C7001260000_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001260000_VAL else 0 end as C7001260000_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001260000_VAL else 0 end as C7001260000_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001260000_VAL else 0 end as C7001260000_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001260000_VAL else 0 end as C7001260000_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001260000_VAL else 0 end as C7001260000_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001260000_VAL else 0 end as C7001260000_VAL07,
case when (val >= 50000000000) then C7001260000_VAL else 0 end as C7001260000_VAL08,
case when (val < 1000000000) then C7001227900_VAL else 0 end as C7001227900_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001227900_VAL else 0 end as C7001227900_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001227900_VAL else 0 end as C7001227900_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001227900_VAL else 0 end as C7001227900_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001227900_VAL else 0 end as C7001227900_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001227900_VAL else 0 end as C7001227900_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001227900_VAL else 0 end as C7001227900_VAL07,
case when (val >= 50000000000) then C7001227900_VAL else 0 end as C7001227900_VAL08,
case when (val < 1000000000) then C7001228000_VAL else 0 end as C7001228000_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001228000_VAL else 0 end as C7001228000_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001228000_VAL else 0 end as C7001228000_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001228000_VAL else 0 end as C7001228000_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001228000_VAL else 0 end as C7001228000_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001228000_VAL else 0 end as C7001228000_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001228000_VAL else 0 end as C7001228000_VAL07,
case when (val >= 50000000000) then C7001228000_VAL else 0 end as C7001228000_VAL08,
case when (val < 1000000000) then C7001228100_VAL else 0 end as C7001228100_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001228100_VAL else 0 end as C7001228100_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001228100_VAL else 0 end as C7001228100_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001228100_VAL else 0 end as C7001228100_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001228100_VAL else 0 end as C7001228100_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001228100_VAL else 0 end as C7001228100_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001228100_VAL else 0 end as C7001228100_VAL07,
case when (val >= 50000000000) then C7001228100_VAL else 0 end as C7001228100_VAL08,
case when (val < 1000000000) then C7001530400_VAL else 0 end as C7001530400_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001530400_VAL else 0 end as C7001530400_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001530400_VAL else 0 end as C7001530400_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001530400_VAL else 0 end as C7001530400_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001530400_VAL else 0 end as C7001530400_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001530400_VAL else 0 end as C7001530400_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001530400_VAL else 0 end as C7001530400_VAL07,
case when (val >= 50000000000) then C7001530400_VAL else 0 end as C7001530400_VAL08,
case when (val < 1000000000) then C7001223700_VAL else 0 end as C7001223700_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001223700_VAL else 0 end as C7001223700_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001223700_VAL else 0 end as C7001223700_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001223700_VAL else 0 end as C7001223700_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001223700_VAL else 0 end as C7001223700_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001223700_VAL else 0 end as C7001223700_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001223700_VAL else 0 end as C7001223700_VAL07,
case when (val >= 50000000000) then C7001223700_VAL else 0 end as C7001223700_VAL08,
case when (val < 1000000000) then C7001224300_VAL else 0 end as C7001224300_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001224300_VAL else 0 end as C7001224300_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001224300_VAL else 0 end as C7001224300_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001224300_VAL else 0 end as C7001224300_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001224300_VAL else 0 end as C7001224300_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001224300_VAL else 0 end as C7001224300_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001224300_VAL else 0 end as C7001224300_VAL07,
case when (val >= 50000000000) then C7001224300_VAL else 0 end as C7001224300_VAL08,
case when (val < 1000000000) then C7001224400_VAL else 0 end as C7001224400_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001224400_VAL else 0 end as C7001224400_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001224400_VAL else 0 end as C7001224400_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001224400_VAL else 0 end as C7001224400_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001224400_VAL else 0 end as C7001224400_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001224400_VAL else 0 end as C7001224400_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001224400_VAL else 0 end as C7001224400_VAL07,
case when (val >= 50000000000) then C7001224400_VAL else 0 end as C7001224400_VAL08,
case when (val < 1000000000) then C7001224500_VAL else 0 end as C7001224500_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001224500_VAL else 0 end as C7001224500_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001224500_VAL else 0 end as C7001224500_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001224500_VAL else 0 end as C7001224500_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001224500_VAL else 0 end as C7001224500_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001224500_VAL else 0 end as C7001224500_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001224500_VAL else 0 end as C7001224500_VAL07,
case when (val >= 50000000000) then C7001224500_VAL else 0 end as C7001224500_VAL08,
case when (val < 1000000000) then C7001520000_VAL else 0 end as C7001520000_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001520000_VAL else 0 end as C7001520000_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001520000_VAL else 0 end as C7001520000_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001520000_VAL else 0 end as C7001520000_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001520000_VAL else 0 end as C7001520000_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001520000_VAL else 0 end as C7001520000_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001520000_VAL else 0 end as C7001520000_VAL07,
case when (val >= 50000000000) then C7001520000_VAL else 0 end as C7001520000_VAL08,
case when (val < 1000000000) then C7001530100_VAL else 0 end as C7001530100_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001530100_VAL else 0 end as C7001530100_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001530100_VAL else 0 end as C7001530100_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001530100_VAL else 0 end as C7001530100_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001530100_VAL else 0 end as C7001530100_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001530100_VAL else 0 end as C7001530100_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001530100_VAL else 0 end as C7001530100_VAL07,
case when (val >= 50000000000) then C7001530100_VAL else 0 end as C7001530100_VAL08,
case when (val < 1000000000) then C7001225200_VAL else 0 end as C7001225200_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001225200_VAL else 0 end as C7001225200_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001225200_VAL else 0 end as C7001225200_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001225200_VAL else 0 end as C7001225200_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001225200_VAL else 0 end as C7001225200_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001225200_VAL else 0 end as C7001225200_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001225200_VAL else 0 end as C7001225200_VAL07,
case when (val >= 50000000000) then C7001225200_VAL else 0 end as C7001225200_VAL08,
case when (val < 1000000000) then C7001530500_VAL else 0 end as C7001530500_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001530500_VAL else 0 end as C7001530500_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001530500_VAL else 0 end as C7001530500_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001530500_VAL else 0 end as C7001530500_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001530500_VAL else 0 end as C7001530500_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001530500_VAL else 0 end as C7001530500_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001530500_VAL else 0 end as C7001530500_VAL07,
case when (val >= 50000000000) then C7001530500_VAL else 0 end as C7001530500_VAL08,
case when (val < 1000000000) then C7001225300_VAL else 0 end as C7001225300_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001225300_VAL else 0 end as C7001225300_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001225300_VAL else 0 end as C7001225300_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001225300_VAL else 0 end as C7001225300_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001225300_VAL else 0 end as C7001225300_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001225300_VAL else 0 end as C7001225300_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001225300_VAL else 0 end as C7001225300_VAL07,
case when (val >= 50000000000) then C7001225300_VAL else 0 end as C7001225300_VAL08,
case when (val < 1000000000) then C7001530600_VAL else 0 end as C7001530600_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001530600_VAL else 0 end as C7001530600_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001530600_VAL else 0 end as C7001530600_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001530600_VAL else 0 end as C7001530600_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001530600_VAL else 0 end as C7001530600_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001530600_VAL else 0 end as C7001530600_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001530600_VAL else 0 end as C7001530600_VAL07,
case when (val >= 50000000000) then C7001530600_VAL else 0 end as C7001530600_VAL08,
case when (val < 1000000000) then C7001240100_VAL else 0 end as C7001240100_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001240100_VAL else 0 end as C7001240100_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001240100_VAL else 0 end as C7001240100_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001240100_VAL else 0 end as C7001240100_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001240100_VAL else 0 end as C7001240100_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001240100_VAL else 0 end as C7001240100_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001240100_VAL else 0 end as C7001240100_VAL07,
case when (val >= 50000000000) then C7001240100_VAL else 0 end as C7001240100_VAL08,
case when (val < 1000000000) then C7001240200_VAL else 0 end as C7001240200_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001240200_VAL else 0 end as C7001240200_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001240200_VAL else 0 end as C7001240200_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001240200_VAL else 0 end as C7001240200_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001240200_VAL else 0 end as C7001240200_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001240200_VAL else 0 end as C7001240200_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001240200_VAL else 0 end as C7001240200_VAL07,
case when (val >= 50000000000) then C7001240200_VAL else 0 end as C7001240200_VAL08,
case when (val < 1000000000) then C7001250100_VAL else 0 end as C7001250100_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001250100_VAL else 0 end as C7001250100_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001250100_VAL else 0 end as C7001250100_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001250100_VAL else 0 end as C7001250100_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001250100_VAL else 0 end as C7001250100_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001250100_VAL else 0 end as C7001250100_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001250100_VAL else 0 end as C7001250100_VAL07,
case when (val >= 50000000000) then C7001250100_VAL else 0 end as C7001250100_VAL08,
case when (val < 1000000000) then C7001532300_VAL else 0 end as C7001532300_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001532300_VAL else 0 end as C7001532300_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001532300_VAL else 0 end as C7001532300_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001532300_VAL else 0 end as C7001532300_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001532300_VAL else 0 end as C7001532300_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001532300_VAL else 0 end as C7001532300_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001532300_VAL else 0 end as C7001532300_VAL07,
case when (val >= 50000000000) then C7001532300_VAL else 0 end as C7001532300_VAL08,
case when (val < 1000000000) then C7001110000_VAL else 0 end as C7001110000_VAL01,
case when (val >= 1000000000 and val < 3000000000) then C7001110000_VAL else 0 end as C7001110000_VAL02,
case when (val >= 3000000000 and val < 5000000000) then C7001110000_VAL else 0 end as C7001110000_VAL03,
case when (val >= 5000000000 and val < 10000000000) then C7001110000_VAL else 0 end as C7001110000_VAL04,
case when (val >= 10000000000 and val < 20000000000) then C7001110000_VAL else 0 end as C7001110000_VAL05,
case when (val >= 20000000000 and val < 30000000000) then C7001110000_VAL else 0 end as C7001110000_VAL06,
case when (val >= 30000000000 and val < 50000000000) then C7001110000_VAL else 0 end as C7001110000_VAL07,
case when (val >= 50000000000) then C7001110000_VAL else 0 end as C7001110000_VAL08
from (
select
kedcd,
bzno,
supt_fld_cd,
val,
C7001260000_VAL,
C7001227900_VAL,
C7001228000_VAL,
C7001228100_VAL,
C7001530400_VAL,
C7001223700_VAL,
C7001224300_VAL,
C7001224400_VAL,
C7001224500_VAL,
C7001520000_VAL,
C7001530100_VAL,
C7001225200_VAL,
C7001530500_VAL,
C7001225300_VAL,
C7001530600_VAL,
C7001240100_VAL,
C7001240200_VAL,
C7001250100_VAL,
C7001532300_VAL,
C7001110000_VAL
from (
select
a.kedcd,
a.bzno,
'A' as supt_fld_cd,
nvl(a.val, 0) * 1000 as val,
nvl(b.C7001260000_VAL, 0) C7001260000_VAL,
nvl(b.C7001227900_VAL, 0) C7001227900_VAL,
nvl(b.C7001228000_VAL, 0) C7001228000_VAL,
nvl(b.C7001228100_VAL, 0) C7001228100_VAL,
nvl(b.C7001530400_VAL, 0) C7001530400_VAL,
nvl(b.C7001223700_VAL, 0) C7001223700_VAL,
nvl(b.C7001224300_VAL, 0) C7001224300_VAL,
nvl(b.C7001224400_VAL, 0) C7001224400_VAL,
nvl(b.C7001224500_VAL, 0) C7001224500_VAL,
nvl(b.C7001520000_VAL, 0) C7001520000_VAL,
nvl(b.C7001530100_VAL, 0) C7001530100_VAL,
nvl(b.C7001225200_VAL, 0) C7001225200_VAL,
nvl(b.C7001530500_VAL, 0) C7001530500_VAL,
nvl(b.C7001225300_VAL, 0) C7001225300_VAL,
nvl(b.C7001530600_VAL, 0) C7001530600_VAL,
nvl(b.C7001240100_VAL, 0) C7001240100_VAL,
nvl(b.C7001240200_VAL, 0) C7001240200_VAL,
nvl(b.C7001250100_VAL, 0) C7001250100_VAL,
nvl(b.C7001532300_VAL, 0) C7001532300_VAL,
nvl(b.C7001110000_VAL, 0) C7001110000_VAL
from (
--########################################################################
select
a.kedcd,
a.bzno,
b.val
from (
select
a.bzno,
b.kedcd
from tbl0130 a, TBL0002 b
where a.bzno = b.bzno(+)
and a.supt_fld_cd = 'A'
and a.sen_ymd like '2012%'
group by a.bzno, b.kedcd
) a, (
select
kedcd,
acct_dt,
val,
no
from (
select
kedcd,
acct_dt,
val,
row_number() over(partition by kedcd order by kedcd, acct_dt desc) as no
from tbl0058
where kedcd in (
select
distinct
b.kedcd
from tbl0130 a, TBL0002 b
where a.bzno = b.bzno
and a.supt_fld_cd = 'A'
and a.sen_ymd like '2012%'
)
and ACCT_DT like '2012%'
and ACCT_CCD = 'Y'
and ACC_CD_FULL = '7001210000'
)
where no = 1
) b
where a.kedcd = b.kedcd(+)
--########################################################################
) a, (
select
kedcd,
acct_dt01 as acct_dt,
acc_cd_full01 as acc_cd_full,
C7001260000_VAL,
C7001227900_VAL,
C7001228000_VAL,
C7001228100_VAL,
C7001530400_VAL,
C7001223700_VAL,
C7001224300_VAL,
C7001224400_VAL,
C7001224500_VAL,
C7001520000_VAL,
C7001530100_VAL,
C7001225200_VAL,
C7001530500_VAL,
C7001225300_VAL,
C7001530600_VAL,
C7001240100_VAL,
C7001240200_VAL,
C7001250100_VAL,
C7001532300_VAL,
C7001110000_VAL
from (
select
kedcd,
max(decode(ACC_CD_FULL, 'C7001260000', acct_dt)) acct_dt01,
max(decode(ACC_CD_FULL, 'C7001227900', acct_dt)) acct_dt02,
max(decode(ACC_CD_FULL, 'C7001228000', acct_dt)) acct_dt03,
max(decode(ACC_CD_FULL, 'C7001228100', acct_dt)) acct_dt04,
max(decode(ACC_CD_FULL, 'C7001530400', acct_dt)) acct_dt05,
max(decode(ACC_CD_FULL, 'C7001223700', acct_dt)) acct_dt06,
max(decode(ACC_CD_FULL, 'C7001224300', acct_dt)) acct_dt07,
max(decode(ACC_CD_FULL, 'C7001224400', acct_dt)) acct_dt08,
max(decode(ACC_CD_FULL, 'C7001224500', acct_dt)) acct_dt09,
max(decode(ACC_CD_FULL, 'C7001520000', acct_dt)) acct_dt10,
max(decode(ACC_CD_FULL, 'C7001530100', acct_dt)) acct_dt11,
max(decode(ACC_CD_FULL, 'C7001225200', acct_dt)) acct_dt12,
max(decode(ACC_CD_FULL, 'C7001530500', acct_dt)) acct_dt13,
max(decode(ACC_CD_FULL, 'C7001225300', acct_dt)) acct_dt14,
max(decode(ACC_CD_FULL, 'C7001530600', acct_dt)) acct_dt15,
max(decode(ACC_CD_FULL, 'C7001240100', acct_dt)) acct_dt16,
max(decode(ACC_CD_FULL, 'C7001240200', acct_dt)) acct_dt17,
max(decode(ACC_CD_FULL, 'C7001250100', acct_dt)) acct_dt18,
max(decode(ACC_CD_FULL, 'C7001532300', acct_dt)) acct_dt19,
max(decode(ACC_CD_FULL, 'C7001110000', acct_dt)) acct_dt20,
max(decode(ACC_CD_FULL, 'C7001260000', acc_cd_full)) acc_cd_full01,
max(decode(ACC_CD_FULL, 'C7001227900', acc_cd_full)) acc_cd_full02,
max(decode(ACC_CD_FULL, 'C7001228000', acc_cd_full)) acc_cd_full03,
max(decode(ACC_CD_FULL, 'C7001228100', acc_cd_full)) acc_cd_full04,
max(decode(ACC_CD_FULL, 'C7001530400', acc_cd_full)) acc_cd_full05,
max(decode(ACC_CD_FULL, 'C7001223700', acc_cd_full)) acc_cd_full06,
max(decode(ACC_CD_FULL, 'C7001224300', acc_cd_full)) acc_cd_full07,
max(decode(ACC_CD_FULL, 'C7001224400', acc_cd_full)) acc_cd_full08,
max(decode(ACC_CD_FULL, 'C7001224500', acc_cd_full)) acc_cd_full09,
max(decode(ACC_CD_FULL, 'C7001520000', acc_cd_full)) acc_cd_full10,
max(decode(ACC_CD_FULL, 'C7001530100', acc_cd_full)) acc_cd_full11,
max(decode(ACC_CD_FULL, 'C7001225200', acc_cd_full)) acc_cd_full12,
max(decode(ACC_CD_FULL, 'C7001530500', acc_cd_full)) acc_cd_full13,
max(decode(ACC_CD_FULL, 'C7001225300', acc_cd_full)) acc_cd_full14,
max(decode(ACC_CD_FULL, 'C7001530600', acc_cd_full)) acc_cd_full15,
max(decode(ACC_CD_FULL, 'C7001240100', acc_cd_full)) acc_cd_full16,
max(decode(ACC_CD_FULL, 'C7001240200', acc_cd_full)) acc_cd_full17,
max(decode(ACC_CD_FULL, 'C7001250100', acc_cd_full)) acc_cd_full18,
max(decode(ACC_CD_FULL, 'C7001532300', acc_cd_full)) acc_cd_full19,
max(decode(ACC_CD_FULL, 'C7001110000', acc_cd_full)) acc_cd_full20,
max(decode(ACC_CD_FULL, 'C7001260000', VAL)) C7001260000_VAL,
max(decode(ACC_CD_FULL, 'C7001227900', VAL)) C7001227900_VAL,
max(decode(ACC_CD_FULL, 'C7001228000', VAL)) C7001228000_VAL,
max(decode(ACC_CD_FULL, 'C7001228100', VAL)) C7001228100_VAL,
max(decode(ACC_CD_FULL, 'C7001530400', VAL)) C7001530400_VAL,
max(decode(ACC_CD_FULL, 'C7001223700', VAL)) C7001223700_VAL,
max(decode(ACC_CD_FULL, 'C7001224300', VAL)) C7001224300_VAL,
max(decode(ACC_CD_FULL, 'C7001224400', VAL)) C7001224400_VAL,
max(decode(ACC_CD_FULL, 'C7001224500', VAL)) C7001224500_VAL,
max(decode(ACC_CD_FULL, 'C7001520000', VAL)) C7001520000_VAL,
max(decode(ACC_CD_FULL, 'C7001530100', VAL)) C7001530100_VAL,
max(decode(ACC_CD_FULL, 'C7001225200', VAL)) C7001225200_VAL,
max(decode(ACC_CD_FULL, 'C7001530500', VAL)) C7001530500_VAL,
max(decode(ACC_CD_FULL, 'C7001225300', VAL)) C7001225300_VAL,
max(decode(ACC_CD_FULL, 'C7001530600', VAL)) C7001530600_VAL,
max(decode(ACC_CD_FULL, 'C7001240100', VAL)) C7001240100_VAL,
max(decode(ACC_CD_FULL, 'C7001240200', VAL)) C7001240200_VAL,
max(decode(ACC_CD_FULL, 'C7001250100', VAL)) C7001250100_VAL,
max(decode(ACC_CD_FULL, 'C7001532300', VAL)) C7001532300_VAL,
max(decode(ACC_CD_FULL, 'C7001110000', VAL)) C7001110000_VAL
from (
select
kedcd,
acct_dt,
acc_cd_full,
val,
no
from (
select
kedcd,
acct_dt,
'C' || acc_cd_full as acc_cd_full,
val,
row_number() over(partition by acc_cd_full, kedcd order by kedcd, acct_dt desc) as no
from (
SELECT
KEDCD,
ACCT_DT,
ACC_CD_FULL,
VAL
FROM TBL0058
WHERE 1 = 1
AND KEDCD in (
select
distinct
b.kedcd
from tbl0130 a, TBL0002 b
where a.bzno = b.bzno
and a.supt_fld_cd = 'A'
and a.sen_ymd like '2012%'
)
AND ACCT_CCD = 'Y'
AND ACC_CD_FULL IN ('7001260000','7001227900','7001228000','7001228100','7001530400','7001223700','7001224300','7001224400','7001224500','7001520000','7001530100','7001225200','7001530500','7001225300','7001530600','7001240100','7001240200','7001250100','7001532300','7001110000')
AND ACCT_DT LIKE '2012%'
)
)
where no = 1
)
group by kedcd
)
where 1 = 1
) b
where a.kedcd = b.kedcd
)
)
)
group by supt_fld_cd
), (
SELECT
LEVEL LV
FROM DUAL
CONNECT BY LEVEL <= 160
)
|
