DB2] 함수이용

1. rownum

 

select * from (select deptname, rownumber() over() rn from org) a where rn<3

DEPTNAME       RN                 

-------------- --------------------

Head Office                       1

New England                       2

  2 record(s) selected.

 

 

select deptname from org fetch first 2 rows only

DEPTNAME     

--------------

Head Office  

New England  

  2 record(s) selected.

 

 

2. date 

 

 

select hex(current date) from sysibm.sysdummy1

1      

--------

20070216

  1 record(s) selected.

 

 

select '^^' from sysibm.sysdummy1 where '20070216' between hex(current date) and hex(current date -5 days)

1

--

  0 record(s) selected.

 

SUBSTR(CHAR(HEX(CURRENT TIMESTAMP)),1,14)

 

3. nvl

 

SQL SELECT 절에서 collesce(col1,널대체값) 또는 value(col1,널대체값)

 

4. instr

userid >>  test:1234

                test:123

select substr(userid, LOCATE( ':', userid )+1) from test_table;

1234

123

 

 

5.rownum

 

db2 "select * from (select a.*,rownumber() over() rn from staff a fetch first 1 rows only )a where rn=0"

6. sequence

"일련번호" DECIMAL(15,0) NOT NULL GENERATED ALWAYS AS IDENTITY ( 
                    START WITH +1 
                    INCREMENT BY +1 
                    MINVALUE +1 
                    MAXVALUE +999999999999999 
                    NO CYCLE 
                    CACHE 20 
                    NO ORDER ) )   
 
  그리고 db에서 제공하는 sequence를 사용하는 방법도 있습니다.