[oracle] decode 함수

DECODE 함수

오라클에서만 사용할 수 있으며 SELECT 문장에서 비교연산을 수행한다.

DECODE(컬럼명, '조건', 참, 거짓)

​DECODE(컬럼명, '조건1', 조건1의 참, '조건2', 조건2​의 참, ...)

DECODE내에 중복 사용가능하다​

  SELECT A.CS_START_DTIME AS "pre.presenceDtime",

         C.TOTAL_CNT AS "pre.applyCount",

         COUNT (DECODE (B.CX_PRESENCE_TYPE_CD, 'P', 1, NULL))

            AS "pre.presenceCount",

         COUNT (B.CX_LATENESS_DTIME) AS "pre.latenessCount",

         COUNT (B.CX_EARLY_DTIME) AS "pre.earlyCount",

         COUNT (B.CX_GOOUT_DTIME) AS "pre.gooutCount",

         COUNT (DECODE (B.CX_PRESENCE_TYPE_CD, 'A', 1, NULL))

            AS "pre.absenceCount",

         DECODE (COUNT (B.CX_COURSE_PRESENCE_SEQ), 0, 'N', 'Y')

            AS "pre.existYn"

    FROM CS_COURSE_ACTIVE_ELEMENT A

         LEFT OUTER JOIN CX_COURSE_APPLY_PRESENCE B

            ON A.CS_START_DTIME = B.CX_PRESENCE_DTIME

         INNER JOIN (  SELECT COUNT (CS_COURSE_ACTIVE_SEQ) AS TOTAL_CNT,

                              CS_COURSE_ACTIVE_SEQ

                         FROM CS_COURSE_APPLY

                        WHERE     CS_DELETE_YN = 'N'

                              AND CS_COURSE_ACTIVE_SEQ = 10

                              AND CS_APPLY_STATUS_CD = 'approval'

                     GROUP BY CS_COURSE_ACTIVE_SEQ) C

            ON A.CS_COURSE_ACTIVE_SEQ = C.CS_COURSE_ACTIVE_SEQ

   WHERE     A.CS_COURSE_ACTIVE_SEQ = 10

         AND A.CS_DELETE_YN = 'N'

         AND A.CS_REFERENCE_TYPE_CD = 'subject'

GROUP BY A.CS_START_DTIME, C.TOTAL_CNT

ORDER BY A.CS_START_DTIME