[Oracle] 비교절에 현재시간 + 문자열 넣기

HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-12' FROM DUAL)

 

 

SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
        SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월",
        SUBQUERY9.NUM AS "9월", SUBQUERY10.NUM AS "10월", SUBQUERY11.NUM AS "11월", SUBQUERY12.NUM AS "12월"
FROM (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY8,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY9,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-10'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY10,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-11'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY11,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = (SELECT TO_CHAR(SYSDATE, 'YYYY')||'-12' FROM DUAL)
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY12
WHERE ROWNUM <=1;