두개의 쿼리를 원하는 모양으로 합치고싶은데 UNION ALL 을 쓰면 안되고
하나의 쿼리로 만들면 원하는 형태의 값이 나오질 않습니다
[첫번째 쿼리문]
SELECT TO_CHAR(regi_date, 'YYYY') AS date1 , COUNT(DECODE(smpl_smplkind, 'SK01', 1)) AS reqSK01 , COUNT(DECODE(smpl_smplkind, 'SK02', 1)) AS reqSK02 FROM tb_smpl_item WHERE diag_req_no IN (SELECT diag_relt_no FROM tb_diag_relt WHERE del_yn = 'N' AND regi_date >= TO_DATE('2009-01-01', 'yyyy-mm-dd') AND regi_date < TO_DATE('2019-04-15', 'yyyy-mm-dd') + 1 ) AND smpl_smplkind IN ('SK01', 'SK02') GROUP BY TO_CHAR(regi_date, 'YYYY') ORDER BY date1
[두번째 쿼리문]
SELECT TO_CHAR(updt_date, 'YYYY') AS updt_date ,COUNT(DECODE(fore_smplkind, 'SK01', 1)) FORESK01 --포유류 ,COUNT(DECODE(fore_smplkind, 'SK02', 1)) FORESK02 --포유류 FROM (SELECT fore_smplkind , updt_date FROM tb_fore WHERE out_yn = 'Y' AND proc_stat = 'PS90' AND fore_smplkind IS NOT NULL AND updt_date >= TO_DATE('2009-01-01', 'yyyy-mm-dd') AND updt_date < TO_DATE('2019-04-15', 'yyyy-mm-dd') + 1 AND fore_smplkind IN ('SK01', 'SK02') ) GROUP BY TO_CHAR(updt_date, 'YYYY') ORDER BY updt_date;
각 쿼리의 결과는 첨부한 이미지 파일대로 나옵니다
최종 결과를
REGI_DATE reqSK01 reqSK01 foreSK01 foreSK02
2017 2 1 0 1
2018 3 4 2 1
2019 1 0 0 0
이렇게 뽑고싶은데 유니온은 컬럼명이 다 나오질 않고
SELECT TO_CHAR(A.regi_date, 'YYYY') AS date1 , COUNT(DECODE(A.smpl_smplkind, 'SK01', 1)) AS reqSK01 , COUNT(DECODE(A.smpl_smplkind, 'SK02', 1)) AS reqSK02 ,COUNT(DECODE(B.fore_smplkind, 'SK01', 1)) FORESK01 --포유류 ,COUNT(DECODE(B.fore_smplkind, 'SK02', 1)) FORESK02 --포유류 FROM tb_smpl_item A, tb_fore B WHERE A.diag_req_no IN (SELECT diag_relt_no FROM tb_diag_relt WHERE del_yn = 'N' AND regi_date >= TO_DATE('2009-01-01', 'yyyy-mm-dd') AND regi_date < TO_DATE('2019-04-15', 'yyyy-mm-dd') + 1 ) AND A.smpl_smplkind IN ('SK01', 'SK02') AND B.out_yn = 'Y' AND B.proc_stat = 'PS90' AND B.fore_smplkind IS NOT NULL AND B.updt_date >= TO_DATE('2009-01-01', 'yyyy-mm-dd') AND B.updt_date < TO_DATE('2019-04-15', 'yyyy-mm-dd') + 1 AND B.fore_smplkind IN ('SK01', 'SK02') GROUP BY TO_CHAR(A.regi_date, 'YYYY'),TO_CHAR(B.updt_date, 'YYYY');
이렇게 하나의 쿼리로 만들었더니 전혀 다른값들이 나와서 데이터가 맞지않습니다..
원하는 결과를 얻기위해서 어디가 문제인지 알수있을까요
SELECT yyyy , NVL(SUM(DECODE(cd, 'SK01', cnt1)), 0) reqsk01 , NVL(SUM(DECODE(cd, 'SK02', cnt1)), 0) reqsk02 , NVL(SUM(DECODE(cd, 'SK01', cnt2)), 0) foresk01 , NVL(SUM(DECODE(cd, 'SK02', cnt2)), 0) foresk02 FROM (SELECT TO_CHAR(regi_date, 'yyyy') yyyy , smpl_smplkind cd , COUNT(*) cnt1 , 0 cnt2 FROM tb_smpl_item WHERE diag_req_no IN (SELECT diag_relt_no FROM tb_diag_relt WHERE del_yn = 'N' AND regi_date >= TO_DATE('2009-01-01', 'yyyy-mm-dd') AND regi_date < TO_DATE('2019-04-15', 'yyyy-mm-dd') + 1 ) AND smpl_smplkind IN ('SK01', 'SK02') GROUP BY TO_CHAR(regi_date, 'yyyy'), smpl_smplkind UNION ALL SELECT TO_CHAR(updt_date, 'yyyy') yyyy , fore_smplkind cd , 0 cnt1 , COUNT(*) cnt2 FROM tb_fore WHERE out_yn = 'Y' AND proc_stat = 'PS90' AND updt_date >= TO_DATE('2009-01-01', 'yyyy-mm-dd') AND updt_date < TO_DATE('2019-04-15', 'yyyy-mm-dd') + 1 AND fore_smplkind IN ('SK01', 'SK02') GROUP BY TO_CHAR(updt_date, 'yyyy'), fore_smplkind ) GROUP BY yyyy ORDER BY yyyy ;