[DATABASE/DB2] ORACLE의 DECODE 함수 기능 사용하기

icodebroker DB2 2024-06-18

■ ORACLE의 DECODE 함수 기능을 사용하는 방법을 보여준다.

▶ 예제 코드 (SQL)


SELECT
    rownumber
   ,CASE
        WHEN zip_code BETWEEN '100091' AND '100091' THEN '91'
        WHEN zip_code BETWEEN '100092' AND '100092' THEN '92'
        WHEN zip_code BETWEEN '100093' AND '100093' THEN '93'
        WHEN zip_code BETWEEN '100094' AND '100094' THEN '94'
        WHEN zip_code BETWEEN '100095' AND '100095' THEN '95'
        ELSE                                             '99'
    END
FROM
(
    SELECT
        zip_code
       ,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
    FROM zipcode
) AS A
WHERE rownumber BETWEEN 20 AND 30
WITH UR;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

SELECT

rownumber

,CASE

WHEN zip_code BETWEEN '100091' AND '100091' THEN '91'

WHEN zip_code BETWEEN '100092' AND '100092' THEN '92'

WHEN zip_code BETWEEN '100093' AND '100093' THEN '93'

WHEN zip_code BETWEEN '100094' AND '100094' THEN '94'

WHEN zip_code BETWEEN '100095' AND '100095' THEN '95'

ELSE '99'

END

FROM

(

SELECT

zip_code

,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber

FROM zipcode

) AS A

WHERE rownumber BETWEEN 20 AND 30

WITH UR;