[SQL] Oracle SQL - NVL, NVL2, DECADE

[DOCKER] MAC에 DOCKER 설치하기

[ORACLE] DOCKER에 ORACLE 11G 설치하기

[POSTGRESQL] DOCKER에 POSTGRESQL 설치하기

 

 

SELECT 
       COMPANY
     , PRODUCT
     , MOBILE
  FROM (
    SELECT 'Google' COMPANY, 'Android' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Google' COMPANY, 'Chrome' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'Macbook' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'iPhone' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Sony' COMPANY, 'walkman' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT, 'Y' MOBILE FROM DUAL
  );

COMPANY PRODUCT M
------- ------- -
Google	Android Y
Google	Chrome
Apple	Macbook
Apple	iPhone	Y
Sony	walkman
Samsung Gallexy Y

6 rows selected.

NVL 함수

MOBILE 컬럼에 Y만 들어있습니다.

NVL함수로 Y, N을 채워봅니다.

 

SELECT 
       COMPANY
     , PRODUCT
     , MOBILE
     , NVL(MOBILE, 'N') AS MOBILE_YN   -- MOBILE이 null이면 N
  FROM (
    SELECT 'Google' COMPANY, 'Android' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Google' COMPANY, 'Chrome' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'Macbook' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'iPhone' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Sony' COMPANY, 'walkman' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT, 'Y' MOBILE FROM DUAL
  );

COMPANY PRODUCT M M
------- ------- - -
Google	Android Y Y
Google	Chrome	  N
Apple	Macbook   N
Apple	iPhone	Y Y
Sony	walkman   N
Samsung Gallexy Y Y

6 rows selected.

NVL2함수

Y와 N을 YES와 NO로 치환합니다.

SELECT 
       COMPANY
     , PRODUCT
     , MOBILE
     , NVL(MOBILE, 'N') AS MOBILE_YN               -- MOBILE이 null이면 N
     , NVL2(MOBILE, 'YES', 'NO') AS MOBILE_YES_NO  -- MOBILE이 있으면 YES, 없으면 NO
  FROM (
    SELECT 'Google' COMPANY, 'Android' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Google' COMPANY, 'Chrome' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'Macbook' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'iPhone' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Sony' COMPANY, 'walkman' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT, 'Y' MOBILE FROM DUAL
  );

COMPANY PRODUCT M M MOB
------- ------- - - ---
Google	Android Y Y YES
Google	Chrome	  N NO
Apple	Macbook   N NO
Apple	iPhone	Y Y YES
Sony	walkman   N NO
Samsung Gallexy Y Y YES

6 rows selected.

 

 

DECADE함수

SELECT 
       COMPANY
     , PRODUCT
     , MOBILE
     , NVL(MOBILE, 'N') AS MOBILE_YN                          -- MOBILE이 null이면 N
     , NVL2(MOBILE, 'YES', 'NO') AS MOBILE_YES_NO             -- MOBILE이 있으면 YES, 없으면 NO
     , DECODE(COMPANY, 'Sony', 'NO_ABE', 'Friend') AS FRIEND  -- COMPANY가 Sony 이면 NO_ABE 아니면 Friend
  FROM (
    SELECT 'Google' COMPANY, 'Android' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Google' COMPANY, 'Chrome' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'Macbook' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Apple' COMPANY, 'iPhone' PRODUCT, 'Y' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Sony' COMPANY, 'walkman' PRODUCT, '' MOBILE FROM DUAL
    UNION ALL
    SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT, 'Y' MOBILE FROM DUAL
  );

COMPANY PRODUCT M M MOB FRIEND
------- ------- - - --- ------
Google	Android Y Y YES Friend
Google	Chrome	  N NO	Friend
Apple	Macbook   N NO	Friend
Apple	iPhone	Y Y YES Friend
Sony	walkman   N NO	NO_ABE
Samsung Gallexy Y Y YES Friend

6 rows selected.