데이터베이스 질문과 답변
by weotjqw [2018.04.11 20:03:39]
안녕하세요 초급 개발자라고 부르기도 창피한 개발자 입니다.
TO_CHAR('','999,999,999' )
천 단위 콤마가 나오는걸 알고 있습니다.
그런데 UNION ALL 여러개 하고 조인 하다보니 퀴리가 복잡해져서요
퀴리 1)
select A.XY_SYSTEM, A.XY_CCODE, A.XY_YEAR, A.XY_QUARTER AS QUARTER,
ROUND(nvl(A.CM_TBUDGET,0) + nvl(B.CM_TBUDGET,0)) AS 합계_계획,
ROUND(nvl(A.PLN_SUM,0) + nvl(B.PLN_SUM,0)) AS 합계_예상,
ROUND(nvl(A.CG_COST_KSUM,0) + nvl(B.CG_COST_KSUM,0)) AS 합계_집행,
ROUND(nvl(A.PLN_SUM,0)) AS 개보수_예상,
ROUND(nvl(A.CG_COST_KSUM,0)) AS 개보수_집행,
ROUND(nvl(B.PLN_SUM,0)) AS 대수선_예상,
ROUND(nvl(B.CG_COST_KSUM,0)) AS 대수선_집행
from ( select 'OPSYSTEMSS' XY_SYSTEM, 'SITE0001' XY_CCODE, 2017 XY_YEAR, X.CDE_NAM XY_QUARTER,
Y.CG_COST_KSUM,
Y.PLN_SUM,
Y.CM_TBUDGET
from M1_COMCODE X, V_M1_EXP_STAT_CJK010 Y
where X.WRK_CDE = 'CSM'
and X.CDE_SYM = 'QUA'
and Y.CG_SYSTEM (+)= 'OPSYSTEMSS'
and Y.CG_CCODE (+)= 'SITE0001'
and Y.CG_YEAR (+)= 2017
and Y.CG_QUARTER (+)= X.ALL_CDE
order by X.CDE_NAM
) A ,
( select 'OPSYSTEMSS' XY_SYSTEM, 'SITE0001' XY_CCODE, 2017 XY_YEAR, X.CDE_NAM XY_QUARTER,
Y.CG_COST_KSUM,
Y.PLN_SUM,
Y.CM_TBUDGET
from M1_COMCODE X, V_M1_EXP_STAT_CJK020 Y
where X.WRK_CDE = 'CSM'
and X.CDE_SYM = 'QUA'
and Y.CG_SYSTEM (+)= 'OPSYSTEMSS'
and Y.CG_CCODE (+)= 'SITE0001'
and Y.CG_YEAR (+)= 2017
and Y.CG_QUARTER (+)= X.ALL_CDE
order by X.CDE_NAM
) B
where B.XY_SYSTEM = A.XY_SYSTEM
and B.XY_CCODE = A.XY_CCODE
and B.XY_YEAR = A.XY_YEAR
and B.XY_QUARTER = A.XY_QUARTER
UNION ALL
select A.XY_SYSTEM, A.XY_CCODE, A.XY_YEAR, '합계' AS QUARTER,
ROUND(sum(nvl(A.CM_TBUDGET,0) + nvl(B.CM_TBUDGET,0) )) AS 합계_계획,
ROUND(sum(nvl(A.PLN_SUM,0) + nvl(B.PLN_SUM,0) )) AS 합계_예상,
ROUND(sum(nvl(A.CG_COST_KSUM,0) + nvl(B.CG_COST_KSUM,0))) AS 합계_집행,
ROUND(sum(nvl(A.PLN_SUM,0))) AS 개보수_예상,
ROUND(sum(nvl(A.CG_COST_KSUM,0))) AS 개보수_집행,
ROUND(sum(nvl(B.PLN_SUM,0))) AS 대수선_예상,
ROUND(sum(nvl(B.CG_COST_KSUM,0))) AS 대수선_집행
from ( select 'OPSYSTEMSS' XY_SYSTEM, 'SITE0001' XY_CCODE, 2017 XY_YEAR, X.ALL_CDE XY_QUARTER,
Y.CG_COST_KSUM,
Y.PLN_SUM,
Y.CM_TBUDGET
from M1_COMCODE X, V_M1_EXP_STAT_CJK010 Y
where X.WRK_CDE = 'CSM'
and X.CDE_SYM = 'QUA'
and Y.CG_SYSTEM (+)= 'OPSYSTEMSS'
and Y.CG_CCODE (+)= 'SITE0001'
and Y.CG_YEAR (+)= 2017
and Y.CG_QUARTER (+)= X.ALL_CDE
) A ,
( select 'OPSYSTEMSS' XY_SYSTEM, 'SITE0001' XY_CCODE, 2017 XY_YEAR, X.ALL_CDE XY_QUARTER,
Y.CG_COST_KSUM,
Y.PLN_SUM,
Y.CM_TBUDGET
from M1_COMCODE X, V_M1_EXP_STAT_CJK020 Y
where X.WRK_CDE = 'CSM'
and X.CDE_SYM = 'QUA'
and Y.CG_SYSTEM (+)= 'OPSYSTEMSS'
and Y.CG_CCODE (+)= 'SITE0001'
and Y.CG_YEAR (+)= 2017
and Y.CG_QUARTER (+)= X.ALL_CDE
) B
where B.XY_SYSTEM = A.XY_SYSTEM
and B.XY_CCODE = A.XY_CCODE
and B.XY_YEAR = A.XY_YEAR
and B.XY_QUARTER = A.XY_QUARTER
group by A.XY_SYSTEM, A.XY_CCODE, A.XY_YEAR
UNION ALL
select A.XY_SYSTEM, A.XY_CCODE, A.XY_YEAR, '집행율(%)' AS QUARTER,
ROUND(decode( sum(nvl(A.PLN_SUM,0) + nvl(B.PLN_SUM,0)), 0, 0,
( sum(nvl(A.CG_COST_KSUM,0) + nvl(B.CG_COST_KSUM,0)) / sum(nvl(A.PLN_SUM,0) + nvl(B.PLN_SUM,0)) ) * 100),1) AS 합계_예상,
0 PLN_SUM_12,
0 CG_COST_KSUM_12,
ROUND(decode( sum(nvl(A.PLN_SUM,0)), 0, 0,
( sum(nvl(A.CG_COST_KSUM,0)) / sum(nvl(A.PLN_SUM,0)) ) * 100),1) AS 개보수_예상,
0 COST_KSUM_1,
ROUND(decode( sum(nvl(B.PLN_SUM,0)), 0, 0,
( sum(nvl(B.CG_COST_KSUM,0)) / sum(nvl(B.PLN_SUM,0)) ) * 100),1) AS 대보수_예상,
0 COST_KSUM_2
from ( select 'OPSYSTEMSS' XY_SYSTEM, 'SITE0001' XY_CCODE, 2017 XY_YEAR, X.ALL_CDE XY_QUARTER,
Y.CG_COST_KSUM,
Y.PLN_SUM,
Y.CM_TBUDGET
from M1_COMCODE X, V_M1_EXP_STAT_CJK010 Y
where X.WRK_CDE = 'CSM'
and X.CDE_SYM = 'QUA'
and Y.CG_SYSTEM (+)= 'OPSYSTEMSS'
and Y.CG_CCODE (+)= 'SITE0001'
and Y.CG_YEAR (+)= 2017
and Y.CG_QUARTER (+)= X.ALL_CDE
) A ,
( select 'OPSYSTEMSS' XY_SYSTEM, 'SITE0001' XY_CCODE, 2017 XY_YEAR, X.ALL_CDE XY_QUARTER,
Y.CG_COST_KSUM,
Y.PLN_SUM,
Y.CM_TBUDGET
from M1_COMCODE X, V_M1_EXP_STAT_CJK020 Y
where X.WRK_CDE = 'CSM'
and X.CDE_SYM = 'QUA'
and Y.CG_SYSTEM (+)= 'OPSYSTEMSS'
and Y.CG_CCODE (+)= 'SITE0001'
and Y.CG_YEAR (+)= 2017
and Y.CG_QUARTER (+)= X.ALL_CDE
) B
where B.XY_SYSTEM = A.XY_SYSTEM
and B.XY_CCODE = A.XY_CCODE
and B.XY_YEAR = A.XY_YEAR
and B.XY_QUARTER = A.XY_QUARTER
group by A.XY_SYSTEM, A.XY_CCODE, A.XY_YEAR
아래 사진 첨부 처럼 잘 나옵니다
제가 원하는것은 출력된 값에 천단위 콤마 찍는것인데
아무리 해봐도 안되서 고수님들의 조언을 듣고자 합니다.
SELECT TO_CHAR(60000000,'FM999,999,999' ) FROM DUAL 천단위 콤마는 되실테고
SQL 복잡한건 ROLLUP 이나 CUBE 써야할것 같네요.
난독증이 있어서... 결과 나온 것에 콤마만 찍으면 된다면,
select A.XY_SYSTEM, A.XY_CCODE, A.XY_YEAR, A.XY_QUARTER AS QUARTER,
CHAR(ROUND(nvl(A.CM_TBUDGET,0) + nvl(B.CM_TBUDGET,0)), 'FM999,999,999,999,999') AS 합계_계획,
CHAR(ROUND(nvl(A.PLN_SUM,0) + nvl(B.PLN_SUM,0)), 'FM999,999,999,999,999') AS 합계_예상,
.....
이하 동일 패턴
참조
https://docs.oracle.com/cd/B19306_01/server.102/b14195/sqlqr07.htm#CHDIHDDB
http://www.gurubee.net/lecture/1027
답 찾았습니다 결국엔 알고 있던것 처럼 컬럼에만 찍으면 되는데 UNION 한 컬럼에 다찍으면 되는거 였네용 감사합니다!
SELECT xy_system
, xy_ccode
, xy_year
, DECODE(g, 0, NVL(xy_quarter, '합계'), '집행율(%)') AS quarter
, DECODE(g, 0, TO_CHAR(c1, 'fm9,999,999,990')
, TO_CHAR(NVL(c3 / NULLIF(c2, 0), 0), 'fm9,990.0')) AS 합계_계획
, DECODE(g, 0, TO_CHAR(c2, 'fm9,999,999,990')) AS 합계_예상
, DECODE(g, 0, TO_CHAR(c3, 'fm9,999,999,990')) AS 합계_집행
, DECODE(g, 0, TO_CHAR(a2, 'fm9,999,999,990')
, TO_CHAR(NVL(a3 / NULLIF(a2, 0), 0), 'fm9,990.0')) AS 개보수_예상
, DECODE(g, 0, TO_CHAR(a3, 'fm9,999,999,990')) AS 개보수_집행
, DECODE(g, 0, TO_CHAR(b2, 'fm9,999,999,990')
, TO_CHAR(NVL(b3 / NULLIF(b2, 0), 0), 'fm9,990.0')) AS 대수선_예상
, DECODE(g, 0, TO_CHAR(b3, 'fm9,999,999,990')) AS 대수선_집행
FROM (SELECT x.xy_system
, x.xy_ccode
, x.xy_year
, x.xy_quarter
, NVL(SUM(a.cm_tbudget ), 0) + NVL(SUM(b.cm_tbudget ), 0) c1
, NVL(SUM(a.pln_sum ), 0) + NVL(SUM(b.pln_sum ), 0) c2
, NVL(SUM(a.cg_cost_ksum), 0) + NVL(SUM(b.cg_cost_ksum), 0) c3
, NVL(SUM(a.pln_sum ), 0) a2
, NVL(SUM(a.cg_cost_ksum), 0) a3
, NVL(SUM(b.pln_sum ), 0) b2
, NVL(SUM(b.cg_cost_ksum), 0) b3
, GROUPING(1) g
FROM (SELECT 'OPSYSTEMSS' xy_system
, 'SITE0001' xy_ccode
, 2017 xy_year
, cde_nam xy_quarter
, all_cde
FROM m1_comcode
WHERE wrk_cde = 'CSM'
AND cde_sym = 'QUA'
) x
, v_m1_exp_stat_cjk010 a
, v_m1_exp_stat_cjk020 b
WHERE x.xy_system = a.cg_system (+)
AND x.xy_ccode = a.cg_ccode (+)
AND x.xy_year = a.cg_year (+)
AND x.all_cde = a.cg_quarter(+)
AND x.xy_system = b.cg_system (+)
AND x.xy_ccode = b.cg_ccode (+)
AND x.xy_year = b.cg_year (+)
AND x.all_cde = b.cg_quarter(+)
GROUP BY x.xy_system, x.xy_ccode, x.xy_year
, ROLLUP(1, x.xy_quarter)
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.