쿼리가 넘 길어서 폰트를 줄였습니다. ㅠㅠ;;;
제가 지금 프로젝트하는곳에서 작성한 쿼리인데
with
param as (
select
SEARCH_TYPE,
decode(REG_DT, null,'19900101',REG_DT ) as
REG_DT,
decode(REG_DT2,null,'29991231',REG_DT2) as
REG_DT2,
SEARCH_YYYYMM,
SORT_TYPE
from
(
select
? as SEARCH_TYPE,
? as REG_DT,
? as REG_DT2,
? as SEARCH_YYYYMM,
? as SORT_TYPE
from
dual
)
),
param_gen as (
select
decode(SEARCH_TYPE, 'term' , case when REG_DT >
REG_DT2 then REG_DT2 else REG_DT END,
'month', search_yyyymm||'01',
to_char(sysdate,'YYYYMM')||'01'
) as START_DATE,
decode(SEARCH_TYPE, 'term' , case when REG_DT2 <
REG_DT then REG_DT else REG_DT2 END,
'month',
SEARCH_YYYYMM||to_char(last_day(to_date(SEARCH_YYYYMM,'YYYYMM')),'DD'),
to_char(sysdate,'YYYYMM')||to_char(last_day(sysdate),'DD')
) as END_DATE
from
param a
),
subject_master as (
select
b.GUBUN,
b.MS_TYPE_SEQ,
b.MS_TYPE_NAME,
a.MS_CD_SEQ,
a.MS_CD_NAME
from
(
select
case when MS_TYPE_SEQ < 200 then 'TEAM' else
'PERSON' end as GUBUN,
MS_TYPE_SEQ,
MS_TYPE_NAME
from
IMS_MS_TYPE
where
ISVALID='Y'
) b,
(
select
MS_CD_SEQ,
MS_TYPE_SEQ,
MS_CD_NAME
from
IMS_MS_CD
where
ISVALID='Y'
) a
where
a.MS_TYPE_SEQ = b.MS_TYPE_SEQ
),
html_span1 as (
select
a.gubun,
min(ms_cd_seq) as MS_CD_SEQ,
count(a.gubun) as ROW_SPAN1
from
subject_master a
group
by gubun
),
html_span2 as (
select
a.MS_TYPE_SEQ, min(ms_cd_seq) as MS_CD_SEQ,
count(MS_TYPE_SEQ) as ROW_SPAN2
from
subject_master a
group by
MS_TYPE_SEQ
),
score_master as (
select
b.MS_TYPE_SEQ,
b.MS_CD_SEQ,
count(decode(b.IN_ACT_VALID, 'Y', 1, null))
as CNT_VALID_OKAY,
count(decode(b.IN_ACT_VALID, 'N', 1, null)) as
CNT_VALID_DENY,
count(decode(b.IN_ACT_VALID, 'F', 1, null))
as CNT_VALID_WAIT,
SUM(decode(b.IN_ACT_VALID, 'Y', a.MG_CUM_SCORE, 0))
as SUM_TOTAL
from
IMS_MG_SCORE a,
IMS_MG_ACT b
where
a.MG_ACT_SEQ(+) = b.MG_ACT_SEQ
and
a.reg_dt between to_date((select START_DATE from
param_gen),'YYYYMMDD')
and to_date((select END_DATE from
param_gen),'YYYYMMDD')+1
group by
b.MS_TYPE_SEQ,
b.MS_CD_SEQ
)
select
row_number() over (order by
substr(to_char(a.MS_CD_SEQ),1,1) desc, a.MS_CD_SEQ)
as
RNUM,
case when a.GUBUN = 'TEAM' and c.ROW_SPAN1 is not null then
'팀'
when a.GUBUN = 'PERSON' and c.ROW_SPAN1 is not null
then '개인'
else ''
end as GUBUN,
decode(b.ROW_SPAN2, null, '',
a.MS_TYPE_NAME) as MS_TYPE_NAME,
c.row_span1,
b.ROW_SPAN2,
a.MS_TYPE_SEQ,
a.MS_CD_SEQ,
a.MS_CD_NAME,
d.CNT_VALID_OKAY,
d.CNT_VALID_DENY,
d.CNT_VALID_WAIT,
d.SUM_TOTAL
from
subject_master a,
html_span2 b,
html_span1 c,
score_master d
where
a.MS_CD_SEQ = b.MS_CD_SEQ(+)
and
a.MS_CD_SEQ = c.MS_CD_SEQ(+)
and
a.MS_CD_SEQ = d.MS_CD_SEQ(+)
노랭이로 표시해놓은 저 값이 문젠데,,,,,
분명히 score_master 안에서는 값이 존재하는데,,,,
전체 쿼리를 수행하믄 ㅡㅡ;;;;; 0 이됩니다.
다른 값들은 모두 정상인데요..저것만 저렇네요 ㅠㅠ
혹시 쿼리상에 논리적인 오류나, 기타오류같은거 발견해주실분..
|