|
select rownum as OBJ_SEQ
, OBJ_RMK1 as OBJ_RMK1
, TOTCNT as OBJ_NO1
, CNT as OBJ_NO2
, max(rownum) over (partition by TOTCNT) as OBJ_NO3
, sum(CNT) over (partition by TOTCNT) as OBJ_NO2_SUM
, sum(decode(OBJ_RMK1, null, CNT)) over (partition by TOTCNT) as OBJ_RMK6
, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as CREATE_DT
from (
select obj_rmk1, std, cnt, totcnt, count(*) over (partition by obj_rmk1) as gcnt
from (
select '1' as STD
, ''||OBJ_RMK1 as OBJ_RMK1
, cnt
, sum(cnt) over (order by '1') as totcnt
from ( select /*+ full(MACVIRT02MT) parallel(MACVIRT02MT 2) */ FNNAC_TLGRM_BSDCD as obj_rmk1
, count(*) as cnt
from MACVIRT02MT
where FNNAC_TLGRM_BSDCD is not null
group by FNNAC_TLGRM_BSDCD
)
union all
select distinct '2' as STD
, CODE_PRMVL
, 0 AS CNT
, 0 AS TOTCNT
from MCMCODE02MT
where CODE_ID = 'FNNAC_TLGRM_BSDCD'
group by CODE_PRMVL
)
)
where GCNT = 1 and STD = '1'
|