|
남이 짜놓은 쿼리문인대,,,정리하기도 힘드네요...
toad 7.6버젼이 있긴한대 쓰지를 안아서 기능을 잘 모르겠네요..
쿼리문 정리해 주는 기능이 어느 메뉴에 있나요?
select c.bungi, nvl(pho_cnt,0) pho_cnt, nvl(sup_cnt,0) sup_cnt,
nvl(usr_cnt,0) usr_cnt, nvl(vst_cnt,0) vst_cnt, nvl(vst_gen,0)
vst_gen, nvl(vst_com,0) vst_com from(select a.bungi, pho_cnt,
pho_tot, sup_cnt, sup_tot, vst_cnt, vst_gen, vst_com
from(select y.bungi bungi, pho_cnt, pho_tot from (select
bungi, count(cnt)-1 pho_cnt, max(total) pho_tot
from(select subStr(log_date, 1,4) ||
ceil(subStr(log_date, 6,2)/3) bungi,
count(log_id) cnt,
decode(count(log_date) ,null,-1,nvl(rownum,-2)) total
from apms_web_log group by
rollup(subStr(log_date, 1,4) ||
ceil(subStr(log_date, 6,2)/3), rownum)) group by
bungi) x, (select distinct(bungi) bungi
from((select subStr(log_date, 1,4) ||
ceil(subStr(log_date, 6,2)/3) bungi from
apms_web_log group by subStr(log_date, 1,4) ||
ceil(subStr(log_date, 6,2)/3))
union all (select
to_char(req_date, 'yyyy') ||
ceil(to_char(req_date, 'mm')/3) bungi from
apms_supply_web) union all
(select to_char(today, 'yyyy') ||
ceil(to_char(today, 'mm')/3) bungi from
apms_counter_web)) ) y where x.bungi (+)=
y.bungi order by y.bungi) a, (select y.bungi
bungi, sup_cnt, sup_tot , vst_cnt, vst_gen, vst_com from
(select bungi , count(cnt)-1 sup_cnt, max(total) sup_tot
from(select to_char(req_date, 'yyyy') ||
ceil(to_char(req_date, 'mm')/3) bungi,
sum(tcount) cnt,
decode(count(req_date) ,null,-1,nvl(rownum,-2)) total
from (select * from
apms_supply_web )
group by rollup(to_char(req_date, 'yyyy') ||
ceil(to_char(req_date,'mm')/3),rownum))
group by bungi ) x, (select
to_char(today, 'yyyy') ||
ceil(to_char(today, 'mm')/3) bungi, count(*) vst_cnt,
sum(decode(power_id, '4',1,0)) vst_gen,
sum(decode(power_id, '3',1,0)) vst_com from
apms_counter_web a, apms_usr_web b where a.web_id =
b.web_id group by to_char(today, 'yyyy') ||
ceil(to_char(today, 'mm')/3) )y
where y.bungi (+)= x.bungi ) b where b.bungi
(+)= a.bungi) c, (select bungi, usr usr_cnt from(select
subStr(log_date, 1,4) || ceil(subStr(log_date,
6,2)/3) bungi, count(distinct(web_id)) usr
from apms_web_log group by subStr(log_date, 1,4)||
ceil(subStr(log_date, 6,2)/3)) ) d where d.bungi (+)= c.bungi
and d.bungi is not null and c.bungi is not null" + ' and ' +
WHERE_PART
|
