[DB2] 자주 사용하는 쿼리 정리

1. Dummy 테이블 - 현재 시간 출력

/* Dummy 테이블을 이용한 현재 시간 출력 */
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS.FF4') AS NOW_DATE
     , TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF4') AS NOW_TIMESTAMP
  FROM SYSIBM.SYSDUMMY1

 

 

2. WITH순환절 - 10일치 날짜 출력

/* WITH순환절을 이용한 10일치 날짜 출력 */
WITH RECURSIVE_DATE ( NUM, DATE ) AS (
  SELECT 1 AS NUM
       , TO_CHAR(SYSDATE, 'YYYYMMDD') AS DATE
    FROM SYSIBM.SYSDUMMY1
   UNION ALL
  SELECT T1.NUM + 1 AS NUM
       , TO_CHAR(TO_DATE(T1.DATE, 'YYYYMMDD')-1, 'YYYYMMDD') AS DATE
    FROM RECURSIVE_DATE T1
   WHERE T1.NUM < 10 /* 종료조건 */
)
SELECT T1.NUM
     , T1.DATE
  FROM RECURSIVE_DATE T1

 

 

3. FETCH FIRST ~ ROWS ONLY - 조회된 결과에서 특정 갯수만 출력

/* 조회된 결과에서 특정 갯수만 출력 */
WITH RECURSIVE_DATE ( NUM, DATE ) AS (
  SELECT 1 AS NUM
       , TO_CHAR(SYSDATE, 'YYYYMMDD') AS DATE
    FROM SYSIBM.SYSDUMMY1
   UNION ALL
  SELECT T1.NUM + 1 AS NUM
       , TO_CHAR(TO_DATE(T1.DATE, 'YYYYMMDD') - 1 DAY, 'YYYYMMDD') AS DATE
    FROM RECURSIVE_DATE T1
   WHERE T1.NUM < 10 /* 종료조건 */
)
SELECT T1.NUM
     , T1.DATE
  FROM RECURSIVE_DATE T1
 
FETCH FETCH FIRST 5 ROWS ONLY /* 5개만 출력 */

 

 

4. CAST - 컬럼 타입 캐스팅

 

/* 컬럼 타입 캐스팅 */
SELECT --Number to String
       100 AS NUM
     , CAST( 100 AS VARCHAR ) AS CAST_TO_VARCHAR
       -- String to Number
     , '000999.10' AS STR_NUM
     , CAST( '000999.10' AS BIGINT ) AS CAST_TO_BIGINT
     , CAST( '000999.10' AS FLOAT ) AS CAST_TO_FLOAT
       -- String to Date
     , '2020-04-24 11:00:00.123456' AS STR_DATE
     , CAST( '2020-04-24 11:00:00.123456' AS DATE ) AS CAST_TO_DATE
     , CAST( '2020-04-24 11:00:00.123456' AS TIMESTAMP ) AS CAST_TO_TIMESTAMP
  FROM SYSIBM.SYSDUMMY1

 

 

5. LOAD FROM - 로더를 이용한 CURSOR 기반의 데이터 INSERT

/* 로더를 이용한 CURSOR 기반의 데이터 INSERT */
CALL SYSPROC.ADMIN_CMD('
  LOAD FROM (
    WITH RECURSIVE_DATE ( NUM, DATE ) AS (
      SELECT 1 AS NUM
           , TO_CHAR(SYSDATE, ''YYYYMMDD'') AS DATE
        FROM SYSIBM.SYSDUMMY1
       UNION ALL
      SELECT T1.NUM + 1 AS NUM
           , TO_CHAR(TO_DATE(T1.DATE, ''YYYYMMDD'')-1, ''YYYYMMDD'') AS DATE
        FROM RECURSIVE_DATE T1
       WHERE T1.NUM < 10 /* 종료조건 */
    )
    SELECT T1.NUM
         , T1.DATE
      FROM RECURSIVE_DATE T1
  ) OF CURSOR INSERT INTO "SCHEMA"."TABLE" NONRECOVERABLE
');
-- ROWS_READ, ROWS_SKIPPED, ROWS_LOADED, ROWS_REJECTED, ROWS_DELETED, ROWS_COMITTED 등의 결과를 반환

 

 

6. ESCAPE - LIKE로 %가 포함된 데이터 조회

/* LIKE로 %가 포함된 데이터 조회 */
WITH RECURSIVE_DATE ( NUM, DATA ) AS (
  SELECT 10 AS NUM
       , '100%' AS DATA
    FROM SYSIBM.SYSDUMMY1
   UNION ALL
  SELECT T1.NUM - 1 AS NUM
       , TO_CHAR((T1.NUM-1)*10) AS DATA
    FROM RECURSIVE_DATE T1
   WHERE T1.NUM > 0 /* 종료조건 */
)
SELECT T1.NUM
     , T1.DATA
  FROM RECURSIVE_DATE T1
 WHERE 1=1
   AND T1.DATA LIKE '__\%' ESCAPE '\'