WITH TA AS (
SELECT '11' AS CHK1 , '12' AS CHK2 , '13' AS CHK3 , '14' AS CHK4
FROM DUAL
UNION ALL
SELECT '21' AS CHK1 , '22' AS CHK2 , '23' AS CHK3 , '24' AS CHK4
FROM DUAL
UNION ALL
SELECT '' AS CHK1 , '32' AS CHK2 , '33' AS CHK3 , '34' AS CHK4
FROM DUAL
UNION ALL
SELECT '' AS CHK1 , '' AS CHK2 , '43' AS CHK3 , '44' AS CHK4
FROM DUAL
UNION ALL
SELECT '' AS CHK1 , '' AS CHK2 , '' AS CHK3 , '54' AS CHK4
FROM DUAL
)
SELECT
NVL(CHK1, CHK2) AS FN_NVL --//CHK1이 NULL이면, CHK2
, NVL2(CHK1, CHK2, CHK3) AS FN_NVL2 --//CHK1이 NULL이 아니면, CHK2 아니면 CHK3
, DECODE(CHK1, NULL, CHK2, CHK3) AS FN_DECODE --//CHK1가 NULL이면, CHK2 아니면 CHK3
, NULLIF(CHK1, '21') AS FN_NULLIF --//CHK1가 '21'값이면, NULL
, COALESCE(CHK1, CHK2, CHK3, CHK4) AS FN_COALESCE --//지정 순서대로 NULL값이 아닌값을 출력
, CHK1, CHK2, CHK3, CHK4
FROM TA
'DB-Oracle' 카테고리의 다른 글
DBMS_RANDOM 렌덤값처리 (0) | 2023.08.08 |
---|---|
Oracle ROW_NUMBER, MIN, MAX, SUM, FIRST_VALUE, LAST_VALUE, LAG, LEAD (0) | 2023.08.08 |
PL-SQL join에 대한 이해 (0) | 2023.07.25 |
특정문자가 포함된 스트링을 ROW단위 컬럼으로 출력하기 (0) | 2023.07.25 |
Oracle 세션별 유저정보 획득하기 SYS_CONTEXT (0) | 2023.06.02 |