시소당

[DB2]오라클과 DB2 명령 비교

--테이블 필드 목록보기
--Oracle : desc act
--DB2 : describe table ACT;

--DB의 테이블 목록보기
--Oracle : select * from user_tables
--DB2 : select * from syscat.tables where tabschema='KIMSUNGBAI' order by tabname;

--N개 로우만 읽어오기
--Oracle : select * from tab where rownum <= 2
--DB2 : select * from act fetch first 2 row only;

--ROWNUM 표시:DB2는 ROWNUM이 없음!(분석함수로 대체)
--Oracle : select rownum,column1..,column2 from tab;
--DB2 : select rownum,actdesc from (select rownumber() over() as rownum,actdesc from KIMSUNGBAI.ACT) temp;

--NVL
--Oracle : select nvl(amt,0) from tab;
--DB2 : SELECT mgrno,COALESCE(mgrno,'널값입니다') FROM KIMSUNGBAI.DEPARTMENT;

--DECODE
--Oracle : select decode(amt,100,'백원','백원이상') from tab;
--DB2 : SELECT case when deptno='A00' then '999' else deptno end as deptno FROM DEPARTMENT;
--DB2 : SELECT case when mgrno is null then '999' else mgrno end as deptno FROM DEPARTMENT;

--SYSDATE와 DUAL
--Oracle : SELECT SYSDATE FROM DUAL;
--DB2 : SELECT CURRENT TIME,CURRENT DATE,CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

--UDF 참조
--TO_DATE() -> DATE()
--TO_CHAR() -> CHAR()
--UPPER() -> UCASE()