CREATE FUNCTION get_SubMenuCnt (pmenu_id VARCHAR(6), prole_id VARCHAR(6) )
 RETURNS INTEGER
 LANGUAGE SQL

 BEGIN ATOMIC
       DECLARE subMenuCnt INTEGER DEFAULT 0;
       SET subMenuCnt =(SELECT COUNT(*) FROM TGA_MENU WHERE UPPR_MENU_ID= pmenu_id  AND ROLE_ID = prole_id);
     RETURN subMenuCnt;
 END;

오라클에서 DB2로 바꿀 때 사용하는 함수들과 UDF

 

1. NVL() => COALESCE()

 

2. DECODE() => CASE .. WHEN .. THEN .. END

 : 이함수는 converting 했을 때 열받았던 함수 이죠.. 다른 함수들은 앞에만 바꿔주면 되는데, 이함수는 일일이 when 절 then 절을 잘 구분해서 하나하나 써줘야 하니 ㅠㅠ 암튼 db2는 이런거는 좀 번거럽습니다.

 

3. ROWNUM => rowid

 : DB2 에서는 ROWNUM 이 없고 대체되는 것이 FETCH FIRST 1 ROW ONLY 라고 나오는데 아무리 써봐도 이거때메 에러 나더라구요~ 그래서 찾은게

FROM 절에 (SELECT ROWNUM() OVER() AS rowid, column1, column2, ... FROM 원래 테이블명 ) as temp 이렇게 써야 하더라구요~

예시) select rowid,b  from (select rownumber() over() as rowid, b from test) temp;

 

4. SYSDATE와 DUAL => CURRENT TIME 과 SYSIBM.SYSDUMMY1

 : 오라클에서의 시간알아볼때나 테이블이 없는경우로 테스트로 쓸 때 DUAL 을 쓰는데요~ DB2에서는 SYSIBM.SYSDUMMY1 으로 쓰죠~

 * SELECT SYSDATE FROM DUAL; => SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1; 으로 바뀔 수 있죠~

 

5. TO_DATE()

 : 그냥 DATE로 바꾸는 함수는 DB2에서 제공하는 함수로는 DATE() 라는 함수가 있는데요, 오라클처럼 쓰고 싶으면 UDF를 만들어서 사용하면 비슷하게 사용할 수 있습니다.(UDF는 참조란) 그런데 DATE는 TIMESTAMP를 많이 사용하는데요, TIMESTAMP_ISO(DATE | TIME | TIMESTAMP | VARCHAR) 로 TIMESTAMP로 바꿀 수 있습니다.

 

6. TO_CHAR()

 : CHAR() 라는 함수로 CHAR로 바꿀 수 있으며, 오라클 처럼 쓰려면 참조된 UDF로 추가하여 TO_CHAR를 쓸 수 있습니다.

 

7. UPPER() => UCASE()

 

8. 추가해야할 함수들[UDF함수 추가]

 1) LPAD()

 2) MONTHS_BETWEEN()

 3) LASTDAY()

 4) ADD_MONTHS()

 

9. 이건 함수 아니지만, OUTER JOIN 의 (+)은 안먹으니 LEFT OUTER JOIN, RIGHT OUTER JOIN 으로 다 바꿔야 합니다.

 

이 함수들은 주로 썼던 함수들이고요, 이거보다 더 많은 정보를 원하시는 분은 http://www.redbooks.ibm.com/abstracts/SG247048.html?Open 에서 PDF 파일을 받으셔서 참조하세요~

 

[참고란]

-- 첫번째 인자에서 두번째 인자(일)를 더한 날짜를 구한다.
-- 반환값 : DATE - 연산된 날짜를 반환한다.
-- 첫번째 인자 : DATE    - 날짜
-- 두번째 인자 : INTEGER - 일자
CREATE FUNCTION UTS.ADD_DAYS (ORGDATE DATE, ADD INTEGER)
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.ADD_DAYS
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE RTNDATE DATE;
 SET RTNDATE = ORGDATE + ADD days;
 RETURN RTNDATE;
END


;

CREATE FUNCTION UTS.ADD_MONTHS (ORGDATE DATE, ADD INTEGER)
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.ADD_MONTHS
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE RTNDATE DATE;
 SET RTNDATE = ORGDATE + ADD month;
 RETURN RTNDATE;
END
;

CREATE FUNCTION UTS.ADD_YEARS (ORGDATE DATE, ADD INTEGER)
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.ADD_YEARS
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE RTNDATE DATE;
 SET RTNDATE = ORGDATE + ADD years;
 RETURN RTNDATE;
END
;

-- 첫번째 인자일자에 두번째인자월을 더한 년월의 월말일을 구한다.
-- 반환값 : DATE - 연산된 날짜를 반환한다.
-- 첫번째 인자 : DATE       - 날짜
-- 두번째 인자 : INTEGER    - 월말일을 구하고자 하는 상대 개월(+,-가능)

CREATE FUNCTION UTS.AFTMONLASTDAY (MYDATE DATE, MYMONTH INTEGER)
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.AFTMONLASTDAY
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE TEMPDATE DATE;
 DECLARE NEXT_M INTEGER;

-- SET NEXT_M = MONTH(MYDATE)+1+MYMONTH;
 SET NEXT_M = MONTH(MYDATE + 1 MONTHS + MYMONTH MONTHS);
 SET TEMPDATE = MYDATE+MYMONTH MONTHS;

 WHILE (NEXT_M != MONTH(TEMPDATE)) do
   SET TEMPDATE = TEMPDATE + 1 DAYS;
 end while;
   
  RETURN TEMPDATE - 1 DAYS ;
END

;

-- 첫번째 인자일자에 두번째인자월을 더한 년월의 월말일을 구한다.
-- 반환값 : DATE - 연산된 날짜를 반환한다.
-- CHGDATE      IN NUMBER,                         --구법인세변경일
-- CHG2005DATE  IN NUMBER,                         --신법인세변경일
-- BALHAENGILJA   IN BDMASTSUB.BALHAENGILJA%TYPE,  -- 채권 발행일자

CREATE FUNCTION UTS.FUN_BONDIJAGB (CHGDATE INTEGER,
                                   CHG2005DATE INTEGER,
                                   BALHAENGILJA INTEGER,
                                   IJAGYESANGIGAN INTEGER,
                                   IJAGYESANGB CHAR(1),
                                   HALINYUL DECIMAL(7,4),
                                   MEIBILJA INTEGER,
                                   CHEORIILJA INTEGER)
 RETURNS CHAR(2)
 LANGUAGE SQL
 SPECIFIC UTS.FUN_BONDIJAGB
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE RTN_BONDIJAGB CHAR(2);

    IF (BALHAENGILJA >= CHG2005DATE OR MEIBILJA >= CHG2005DATE) THEN 
        SET RTN_BONDIJAGB = '00';
    ELSEIF (BALHAENGILJA < CHG2005DATE
         AND IJAGYESANGB IN ('3','8')
         AND HALINYUL = 0)             THEN
        IF (INTEGER(UTS.ADD_MONTHS(uts.int2date(CHEORIILJA),-1*IJAGYESANGIGAN))
             >= CHG2005DATE )             THEN
            SET RTN_BONDIJAGB = '00';
        ELSE
            SET RTN_BONDIJAGB = '99';
        END IF;
    ELSEIF (IJAGYESANGB IN ('3','8') AND HALINYUL <> 0) THEN
        IF (BALHAENGILJA < CHGDATE) THEN
            SET RTN_BONDIJAGB = '00';
        ELSE
            SET RTN_BONDIJAGB = '99';
        END IF;
    ELSE
        SET RTN_BONDIJAGB = '99';
    END IF;
   
  RETURN RTN_BONDIJAGB;
END

;

CREATE FUNCTION UTS.GETAFTERMONTHLAST (P_GIJUNILJA INTEGER,
                                    P_MONTH INTEGER )
RETURNS INTEGER
LANGUAGE SQL

BEGIN ATOMIC
    DECLARE RTNDATE INTEGER DEFAULT 0;
    SET RTNDATE = CASE
                 WHEN P_GIJUNILJA = 99999999 THEN 99999999
                 ELSE INTEGER(UTS.AFTMONLASTDAY (UTS.INT2DATE(P_GIJUNILJA), P_MONTH))
                 END;
    RETURN (RTNDATE);
END;

-- 시스템명  : 유가증권운용시스템
-- PKG NAME  : 기준일의 익전영업일자 산출 (영업일 기준)
-- 작 성 일  : 2003.02.09
-- 작 성 자  : 박종철
CREATE FUNCTION UTS.GETDAYTRADE(
    P_GIJUNILJA  INTEGER,
    P_ILSU       INTEGER)
 RETURNS INTEGER
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION
 READS SQL DATA
 BEGIN ATOMIC
  DECLARE R_RTNDATE     INTEGER;
  IF P_ILSU > 0 THEN
    IF P_GIJUNILJA >= 20020701 THEN
      SET R_RTNDATE = (SELECT CASE P_GIJUNILJA
                                WHEN 0 THEN 99999999
                                WHEN 99999999 THEN 99999999
                                ELSE MIN(YEAR*10000+MONTH*100+DAY) END
                         FROM CMILJAMS
                        WHERE YEAR*10000+MONTH*100+DAY > P_GIJUNILJA
                          AND GONILGB = 0 AND YOIL <> 6);
    ELSE
      SET R_RTNDATE = (SELECT CASE P_GIJUNILJA
                                WHEN 0 THEN 99999999
                                WHEN 99999999 THEN 99999999
                                ELSE MIN(YEAR*10000+MONTH*100+DAY) END
                         FROM CMILJAMS
                        WHERE YEAR*10000+MONTH*100+DAY > P_GIJUNILJA
                          AND GONILGB = 0 );
    END IF;
  ELSE
    IF P_GIJUNILJA >= 20020701 THEN
      SET R_RTNDATE = (SELECT CASE P_GIJUNILJA
                                WHEN 0 THEN 99999999
                                WHEN 99999999 THEN 99999999
                                ELSE MAX(YEAR*10000+MONTH*100+DAY) END
                         FROM CMILJAMS
                        WHERE YEAR*10000+MONTH*100+DAY < P_GIJUNILJA
                          AND GONILGB = 0 AND YOIL <> 6 );
    ELSE
      SET R_RTNDATE = (SELECT CASE P_GIJUNILJA
                                WHEN 0 THEN 99999999
                                WHEN 99999999 THEN 99999999
                                ELSE MAX(YEAR*10000+MONTH*100+DAY) END
                         FROM CMILJAMS
                        WHERE YEAR*10000+MONTH*100+DAY < P_GIJUNILJA
                          AND GONILGB = 0 );
    END IF;
  END IF;
  RETURN (R_RTNDATE);

 END
;

-- 인자로 받은 년도의 1월 1일을 반환한다.
-- 반환값 : INTEGER - 해당연도의 1월 1일 ex)20060101
-- 인  자 : INTEGER - 날짜(YYYYMMDD 형식)
CREATE FUNCTION UTS.GETFIRSTOFYEAR (ORGDATE INTEGER)
 RETURNS INTEGER
 LANGUAGE SQL
 SPECIFIC UTS.GETFIRSTOFYEAR
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE RTNDATE INTEGER;
 DECLARE VDATE DATE;
 IF ORGDATE = 0 THEN
  SET RTNDATE = 0;
 ELSE
  SET VDATE = UTS.INT2DATE(ORGDATE);
  SET RTNDATE = INTEGER(SUBSTR(CHAR(ORGDATE),1,4) || '0101');
 END IF;
 RETURN RTNDATE;
END
;

CREATE FUNCTION UTS.GETGYEJEONGNAME(
    P_GYEJEONGCODE  VARCHAR(20),
    P_HOSTGUBUN     INTEGER)  --1:HOST , 2:TRAVIS
 RETURNS  VARCHAR(80)
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION
 READS SQL DATA
 BEGIN ATOMIC
  DECLARE R_GYEJEONGNAME     VARCHAR(80);
  DECLARE W_LEVELGB          INTEGER;
  DECLARE W_GYEJEONGNAME     VARCHAR(60);
  DECLARE W_SPACE            VARCHAR(10);
  DECLARE W_CNT              INTEGER;
  SET W_LEVELGB = 0;
  SET W_CNT = 0;
  IF P_HOSTGUBUN = 1 THEN
   SET ( W_LEVELGB , W_GYEJEONGNAME )=(
    SELECT MAX(LEVELGB) AS LEVELGB,MAX(HOSTNAME) AS HOSTNAME
      FROM (SELECT DISTINCT INTEGER(COALESCE(LEVELGB,'0')) AS LEVELGB ,
                   MAX(HOSTCODE) AS HOSTCODE,MAX(HOSTNAME) AS HOSTNAME
              FROM ACMASTER
             WHERE HOSTCODE = P_GYEJEONGCODE
               AND INPUTGB = 'I'
             GROUP BY LEVELGB) A );
  ELSE
   SET ( W_LEVELGB , W_GYEJEONGNAME )=(
    SELECT INTEGER(COALESCE(LEVELGB,'0')) AS LEVELGB ,
           GYEJEONGNAME AS GYEJEONGNAME
      FROM ACMASTER
     WHERE GYEJEONGCODE = P_GYEJEONGCODE);
  END IF;

  IF W_LEVELGB = 0 THEN
   SET R_GYEJEONGNAME = W_GYEJEONGNAME;
  ELSE
   SET W_LEVELGB = W_LEVELGB - 1;
  END IF;

  SET W_SPACE='';
  SET R_GYEJEONGNAME ='';
  WHILE W_CNT <= W_LEVELGB DO
     SET W_SPACE = W_SPACE||' ';
     SET W_CNT = W_CNT + 1;
  END WHILE;
  SET R_GYEJEONGNAME = W_SPACE||W_GYEJEONGNAME;
  RETURN (R_GYEJEONGNAME);

 END
;

CREATE FUNCTION UTS.GETJYGRADE( P_CODE  VARCHAR(15))
 RETURNS  INTEGER
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION
 READS SQL DATA
 BEGIN ATOMIC
  DECLARE R_ORDER     INTEGER;
  DECLARE CODE        VARCHAR(15);

  SET R_ORDER = 0;
  SET CODE = RTRIM(P_CODE);

   IF CODE = 'AAA+'      THEN -- #### A CLASS ##### */
      SET R_ORDER = 100;
   ELSEIF CODE = 'AAA' OR CODE = 'AAA0' THEN
      SET R_ORDER = 104;
   ELSEIF CODE = 'AAA-'   THEN
      SET R_ORDER = 108;
   ELSEIF CODE = 'AA+'    THEN
      SET R_ORDER = 112;
   ELSEIF CODE = 'AA'  OR CODE = 'AA0' OR CODE = 'AA00' THEN
      SET R_ORDER = 116;
   ELSEIF CODE = 'AA-'    THEN
      SET R_ORDER = 120;
   ELSEIF CODE = 'A1+'     THEN
      SET R_ORDER = 124;
   ELSEIF CODE = 'A1'     THEN
      SET R_ORDER = 128;
   ELSEIF CODE = 'A1-'     THEN
      SET R_ORDER = 132;
   ELSEIF CODE = 'A2+'     THEN
      SET R_ORDER = 136;
   ELSEIF CODE = 'A2'     THEN
      SET R_ORDER = 140;
   ELSEIF CODE = 'A2-'     THEN
      SET R_ORDER = 144;
   ELSEIF CODE = 'A3+'     THEN
      SET R_ORDER = 148;
   ELSEIF CODE = 'A3'     THEN
      SET R_ORDER = 152;
   ELSEIF CODE = 'A3-'     THEN
      SET R_ORDER = 156;
   ELSEIF CODE = 'A+'     THEN
      SET R_ORDER = 160;
   ELSEIF CODE = 'A'   OR CODE = 'A0'  OR CODE = 'A00' OR CODE = 'A000' THEN
      SET R_ORDER = 164;
   ELSEIF CODE = 'A-'     THEN
      SET R_ORDER = 168;
   ELSEIF CODE = 'BBB+'      THEN  -- #### B CLASS ##### */
      SET R_ORDER = 200;
   ELSEIF CODE = 'BBB' OR CODE = 'BBB0' THEN
      SET R_ORDER = 204;
   ELSEIF CODE = 'BBB-'   THEN
      SET R_ORDER = 208;
   ELSEIF CODE = 'BB+'    THEN
      SET R_ORDER = 212;
   ELSEIF CODE = 'BB'  OR CODE = 'BB0' OR CODE = 'BB00' THEN
      SET R_ORDER = 216;
   ELSEIF CODE = 'BB-'    THEN
      SET R_ORDER = 220;
   ELSEIF CODE = 'B1+'     THEN
      SET R_ORDER = 224;
   ELSEIF CODE = 'B1'     THEN
      SET R_ORDER = 228;
   ELSEIF CODE = 'B1-'     THEN
      SET R_ORDER = 232;
   ELSEIF CODE = 'B2+'     THEN
      SET R_ORDER = 236;
   ELSEIF CODE = 'B2'     THEN
      SET R_ORDER = 240;
   ELSEIF CODE = 'B2-'     THEN
      SET R_ORDER = 244;
   ELSEIF CODE = 'B3+'     THEN
      SET R_ORDER = 248;
   ELSEIF CODE = 'B3'     THEN
      SET R_ORDER = 252;
   ELSEIF CODE = 'B3-'     THEN
      SET R_ORDER = 256;
   ELSEIF CODE = 'B+'     THEN
      SET R_ORDER = 260;
   ELSEIF CODE = 'B'   OR CODE = 'B0'  OR CODE = 'B00' OR CODE = 'B000' THEN
      SET R_ORDER = 264;
   ELSEIF CODE = 'B-'     THEN
      SET R_ORDER = 268;
   ELSEIF CODE = 'CCC+'      THEN -- #### C CLASS ##### */
      SET R_ORDER = 300;
   ELSEIF CODE = 'CCC' OR CODE = 'CCC0' THEN
      SET R_ORDER = 304;
   ELSEIF CODE = 'CCC-'   THEN
      SET R_ORDER = 308;
   ELSEIF CODE = 'CC+'    THEN
      SET R_ORDER = 312;
   ELSEIF CODE = 'CC'  OR CODE = 'CC0' OR CODE = 'CC00' THEN
      SET R_ORDER = 316;
   ELSEIF CODE = 'CC-'    THEN
      SET R_ORDER = 320;
   ELSEIF CODE = 'C+'     THEN
      SET R_ORDER = 324;
   ELSEIF CODE = 'C'   OR CODE = 'C0'  OR CODE = 'C00' OR CODE = 'C000' THEN
      SET R_ORDER = 328;
   ELSEIF CODE = 'C-'     THEN
      SET R_ORDER = 332;
   ELSEIF CODE = 'DDD+'      THEN -- #### D CLASS ##### */
      SET R_ORDER = 400;
   ELSEIF CODE = 'DDD' OR CODE = 'DDD0' THEN
      SET R_ORDER = 404;
   ELSEIF CODE = 'DDD-'   THEN
      SET R_ORDER = 408;
   ELSEIF CODE = 'DD+'    THEN
      SET R_ORDER = 412;
   ELSEIF CODE = 'DD'  OR CODE = 'DD0' OR CODE = 'DD00' THEN
      SET R_ORDER = 416;
   ELSEIF CODE = 'DD-'    THEN
      SET R_ORDER = 420;
   ELSEIF CODE = 'D+'     THEN
      SET R_ORDER = 424;
   ELSEIF CODE = 'D'   OR CODE = 'D0'  OR CODE = 'D00' OR CODE = 'D000' THEN
      SET R_ORDER = 428;
   ELSEIF CODE = 'D-'     THEN
      SET R_ORDER = 432;
   ELSEIF CODE = 'EEE+'      THEN -- #### D CLASS ##### */
      SET R_ORDER = 500;
   ELSEIF CODE = 'EEE' OR CODE = 'EEE0' THEN
      SET R_ORDER = 504;
   ELSEIF CODE = 'EEE-'   THEN
      SET R_ORDER = 508;
   ELSEIF CODE = 'EE+'    THEN
      SET R_ORDER = 512;
   ELSEIF CODE = 'EE'  OR CODE = 'EE0' OR CODE = 'EE00' THEN
      SET R_ORDER = 516;
   ELSEIF CODE = 'EE-'    THEN
      SET R_ORDER = 520;
   ELSEIF CODE = 'E+'     THEN
      SET R_ORDER = 524;
   ELSEIF CODE = 'E'   OR CODE = 'E0'  OR CODE = 'E00' OR CODE = 'E000' THEN
      SET R_ORDER = 528;
   ELSEIF CODE = 'E-'     THEN
      SET R_ORDER = 532;
   ELSE
      SET R_ORDER = 0;
   END IF;
   RETURN (R_ORDER);
END
;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.GREAT( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
CREATE FUNCTION UTS.GREAT( VAR1 DATE , VAR2 DATE )
 RETURNS DATE
--CREATE FUNCTION UTS.GREAT( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.GREAT( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.GREAT( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.GREAT( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 > VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.GREAT( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.GREAT( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.GREAT( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.GREAT( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.GREAT( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
CREATE FUNCTION UTS.GREAT( VAR1 DECIMAL , VAR2 DECIMAL )
 RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 > VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.GREAT( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.GREAT( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.GREAT( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.GREAT( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
CREATE FUNCTION UTS.GREAT( VAR1 DOUBLE , VAR2 DOUBLE )
 RETURNS DOUBLE
--CREATE FUNCTION UTS.GREAT( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 > VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.GREAT( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.GREAT( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.GREAT( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
CREATE FUNCTION UTS.GREAT( VAR1 INTEGER , VAR2 INTEGER )
 RETURNS INTEGER
--CREATE FUNCTION UTS.GREAT( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.GREAT( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 > VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.GREAT( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.GREAT( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
CREATE FUNCTION UTS.GREAT( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
 RETURNS TIMESTAMP
--CREATE FUNCTION UTS.GREAT( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.GREAT( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.GREAT( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 > VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
CREATE FUNCTION UTS.GREAT( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
 RETURNS VARCHAR(1000)
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 > VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

CREATE FUNCTION UTS.INT2DATE (DATENUM integer)
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.INT2DATE
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
 RETURN
CASE
 WHEN DATENUM > 19000000 THEN
   DATE( SUBSTR(CHAR(DATENUM),1,4)||'-'||
         SUBSTR(CHAR(DATENUM),5,2)||'-'||
         SUBSTR(CHAR(DATENUM),7,2) )
 ELSE DATE('1900-01-01')
END
;

-- 첫번째 인자일자의 월말일을 구한다.
-- 반환값 : DATE - 연산된 날짜를 반환한다.
-- 첫번째 인자 : DATE    - 날짜

CREATE FUNCTION UTS.LASTDAY (MYDATE DATE)
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.LASTDAY
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE TEMPDATE DATE;
 DECLARE NEXT_M INTEGER;

-- SET NEXT_M = MONTH(MYDATE)+1;
 SET NEXT_M = MONTH(MYDATE + 1 MONTHS);

 SET TEMPDATE = MYDATE;

 WHILE (NEXT_M != MONTH(TEMPDATE)) do
--   SET TEMPDATE = TEMPDATE + 1 DAY;
   SET TEMPDATE = TEMPDATE + 1 DAYS;
 end while;
   
  RETURN TEMPDATE - 1 DAY ;
END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.LEAST( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
CREATE FUNCTION UTS.LEAST( VAR1 DATE , VAR2 DATE )
 RETURNS DATE
--CREATE FUNCTION UTS.LEAST( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.LEAST( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.LEAST( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.LEAST( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 < VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.LEAST( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.LEAST( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.LEAST( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.LEAST( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.LEAST( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
CREATE FUNCTION UTS.LEAST( VAR1 DECIMAL , VAR2 DECIMAL )
 RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 < VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.LEAST( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.LEAST( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.LEAST( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.LEAST( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
CREATE FUNCTION UTS.LEAST( VAR1 DOUBLE , VAR2 DOUBLE )
 RETURNS DOUBLE
--CREATE FUNCTION UTS.LEAST( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 < VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.LEAST( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.LEAST( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.LEAST( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
CREATE FUNCTION UTS.LEAST( VAR1 INTEGER , VAR2 INTEGER )
 RETURNS INTEGER
--CREATE FUNCTION UTS.LEAST( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.LEAST( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 < VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
--CREATE FUNCTION UTS.LEAST( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
-- RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.LEAST( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
CREATE FUNCTION UTS.LEAST( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
 RETURNS TIMESTAMP
--CREATE FUNCTION UTS.LEAST( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.LEAST( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.LEAST( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 < VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

-- 인자와 반환값을 DATE, VARCHAR(1000), TIMESTAMP,INTEGER, DOUBLE, DECIMAL
-- 로 수정하여 모두 등록
CREATE FUNCTION UTS.LEAST( VAR1 VARCHAR(1000) , VAR2 VARCHAR(1000) )
 RETURNS VARCHAR(1000)
--CREATE FUNCTION UTS.LEAST( VAR1 DATE , VAR2 DATE )
-- RETURNS DATE
--CREATE FUNCTION UTS.LEAST( VAR1 TIMESTAMP , VAR2 TIMESTAMP )
-- RETURNS TIMESTAMP
--CREATE FUNCTION UTS.LEAST( VAR1 INTEGER , VAR2 INTEGER )
-- RETURNS INTEGER
--CREATE FUNCTION UTS.LEAST( VAR1 DOUBLE , VAR2 DOUBLE )
-- RETURNS DOUBLE
--CREATE FUNCTION UTS.LEAST( VAR1 DECIMAL , VAR2 DECIMAL )
-- RETURNS DECIMAL
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   IF VAR1 < VAR2 THEN
     RETURN VAR1;
   ELSE
     RETURN VAR2;
   END IF;

END

;

--LPAD 이게 주석으로 먹을까???
CREATE FUNCTION UTS.LPAD (C1 VarChar(4000), N integer, C2 VarChar(4000))
 RETURNS VARCHAR(4000)
 LANGUAGE SQL
 SPECIFIC UTS.LPADBase
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
 RETURN
 CASE
   WHEN N > length(C1) THEN
    rtrim(substr(repeat(C2,(N-length(C1)+length(C2))/(length(C2)+1-sign(length(C2)))),1,N-length(C1)) || C1)
   ELSE rtrim(substr(C1,1,N))
 END
;

CREATE FUNCTION UTS.STR2DATE (DATESTR VARCHAR(9))
 RETURNS DATE
 LANGUAGE SQL
 SPECIFIC UTS.STR2DATE
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
 RETURN
CASE
 WHEN LENGTH(DATESTR) = 8 THEN
  UTS.INT2DATE( INTEGER(DATESTR) )
 ELSE DATE('1900-01-01')
END
;

CREATE FUNCTION UTS.TO_CHAR (T1 TIMESTAMP, FORMAT VARCHAR(32))
 RETURNS VARCHAR(26)
 LANGUAGE SQL
 SPECIFIC UTS.TO_CHAR
 DETERMINISTIC
 CONTAINS SQL
 NO EXTERNAL ACTION
BEGIN ATOMIC
 DECLARE CHS_TMSTMP CHAR ( 26 );
 DECLARE RETVAL VARCHAR ( 26 );
 SET CHS_TMSTMP = CHAR ( T1 );

 IF UPPER(UTS.TRIM ( FORMAT )) = 'DD' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,9,2);
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'MM' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,6,2);
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'YYYY' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,1,4);
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'YYYY-MM' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,1,7);
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'YYYY-MM-DD' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,1,10);
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'YYYYMMDD' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,1,4)||SUBSTR(CHS_TMSTMP,6,2)||SUBSTR(CHS_TMSTMP,9,2);
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'D' THEN SET RETVAL = CHAR(DAYOFWEEK(CHS_TMSTMP));
 ELSEIF UPPER(UTS.TRIM ( FORMAT )) = 'HH24MISS' THEN SET RETVAL = SUBSTR (CHS_TMSTMP,12,2)||SUBSTR(CHS_TMSTMP,15,2)||SUBSTR(CHS_TMSTMP,18,2);
 ELSE   SET RETVAL = 'INVALID FORMAT SPECIFIED.' ;
 END IF;
 RETURN RETVAL;
END
;

CREATE FUNCTION UTS.TO_DATE( MYDATE VARCHAR(8) , FMT VARCHAR(32) )
 RETURNS TIMESTAMP  
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
   DECLARE CHS_TMSTMP TIMESTAMP;
   DECLARE TMP_LEN INTEGER;
   IF MYDATE IS NULL THEN
     RETURN CURRENT TIMESTAMP;
   ELSE
     SET TMP_LEN=LENGTH(RTRIM(FMT));
     IF TMP_LEN=8 THEN
       SET TMP_LEN=0;
     END IF;
     IF TMP_LEN=10 THEN
       SET TMP_LEN=0;
     END IF;
     IF TMP_LEN=14 THEN
       SET TMP_LEN=0;
     END IF;
     IF TMP_LEN=19 THEN
       SET TMP_LEN=0;
     END IF;
  END IF;
     RETURN CHS_TMSTMP;

END

;

CREATE FUNCTION UTS.TRIM(INSTR VARCHAR(4000))
 RETURNS  VARCHAR(4000)
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
     DECLARE  TMPSTR VARCHAR(4000) DEFAULT  '';
     DECLARE  REVSTR VARCHAR(4000) DEFAULT  '';
     IF  INSTR IS NULL THEN
         RETURN  NULL;
     END IF;
     SET  TMPSTR = RTRIM(INSTR);
     SET  REVSTR = LTRIM(TMPSTR);
     RETURN  REVSTR;
 END
;

 

DB2 Data Types

Every column in a table has a name and a data type. DB2 data types fall into three categories: types for string data; types for numeric data; and types for dates, times, and timestamps. The categories, followed by the data types within each category, are listed in the following sections. The SAS/ACCESS interface to DB2 handles all DB2 data types. This section describes how the DB2 engine treats each of these data types.

String Data

The DB2 string data types are listed here.

CHAR(n)
specifies a fixed-length column of length n for character string data. The maximum for n is 254.

VARCHAR(n)
specifies a varying-length column for character string data. n specifies the maximum length of the string. If n is greater than 254, the column is a long string column. DB2 imposes some restrictions on referencing long string columns.

LONG VARCHAR
specifies a varying-length column for character string data. DB2 determines the maximum length of this column. A column defined as LONG VARCHAR is always a long string column and, therefore, subject to referencing restrictions.

GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC
specifies graphic strings and is comparable to the types for character strings. However, n specifies the number of double-byte characters, so the maximum value for n is 127. If n is greater than 127, the column is a long string column and is subject to referencing restrictions.

Numeric Data

The DB2 numeric data types are listed here.

SMALLINT
specifies a small integer. Values in a column of this type can range from -32,768 through +32,767.

INTEGER | INT
specifies a large integer. Values in a column of this type can range from -2,147,483,648 through +2,147,483,647.

REAL | FLOAT(n)
specifies a single-precision, floating-point number. If n is omitted or if n is greater than 21, the column is double-precision. Values in a column of this type can range from approximately -7.2E+75 through 7.2E+75.

FLOAT(n) | DOUBLE PRECISION | FLOAT | DOUBLE
specifies a double-precision, floating-point number. n can range from 22 through 53. If n is omitted, 53 is the default. Values in a column of this type can range from approximately -7.2E+75 through 7.2E+75.

DECIMAL(p,s) | DEC(p,s)
specifies a packed-decimal number. p is the total number of digits (precision) and s is the number of digits to the right of the decimal point (scale). The maximum precision is 31 digits. The range of s is 0 s p.

If s is omitted, 0 is assigned and p may also be omitted. Omitting both s and p results in the default DEC(5,0). The maximum range of p is 1 -1031 to 1031 -1.

Even though the DB2 numeric columns have these distinct data types, the DB2 engine accesses, inserts, and loads all numerics as FLOATs.

Dates, Times, and Timestamps

DB2 date and time data types are similar to SAS date and time values in that they are stored internally as numeric values and are displayed in a site-chosen format. The DB2 data types for dates, times, and timestamps are listed here. Note that columns of these data types may contain data values that are out of range for the SAS System, which handles dates from 1582 A.D. through 20,000 A.D.

DATE
specifies date values in the format YYYY-MM-DD. For example, January 25, 1989, is input as 1989-01-25. Values in a column of this type can range from 0001-01-01 through 9999-12-31.

TIME
specifies time values in the format HH.MM.SS. For example, 2:25 p.m. is input as 14.25.00. Values in a column of this type can range from 00.00.00 through 24.00.00.

TIMESTAMP
combines a date and time and adds a microsecond to make a seven-part value of the format YYYY-MM-DD-HH.MM.SS.MMMMMM. For example, a timestamp for precisely 2:25 p.m. on January 25, 1989, is 1989-01-25-14.25.00.000000. Values in a column of this type can range from 0001-01-01-00.00.00.000000 through 9999-12-31-24.00.00.000000.

DB2 NULLs and DB2 Default Values

DB2 has a special value that is called NULL. This value means an absence of information. It is analogous to the SAS System's missing value.

Columns can be defined so that they do not allow NULL data. NOT NULL would indicate, for example, that DB2 does not allow a row to be added to the TESTID.CUSTOMERS table unless there's a value for CUSTOMER.

Columns can also be defined as NOT NULL WITH DEFAULT. The following table lists the default values assigned by DB2 to columns that are defined as NOT NULL WITH DEFAULT. An example of such a column is STATE in TESTID.CUSTOMERS. If a column is omitted from a view descriptor, default values are assigned to the column. However, if a column is specified in a view descriptor and it has no values, no default values are assigned.

Default Values Assigned by DB2 for columns defined as NOT NULL WITH DEFAULT
DB2 Column Type DB2 Default*
CHAR(n) | GRAPHIC(n) blanks, unless the NULLCHARVAL= option is specified
VARCHAR | LONG VARCHAR | VARGRAPHIC | LONG VARGRAPHIC empty string
SMALLINT | INT | FLOAT | DECIMAL | REAL 0
DATE current date, derived from the system clock
TIME current time, derived from the system clock
TIMESTAMP current timestamp, derived from the system clock
*The default values that are listed in this table pertain to values that are assigned by DB2.

Knowing whether a DB2 column allows NULL values or whether DB2 supplies a default value can assist you in writing selection criteria and in entering values to update a table. Unless a column is defined as NOT NULL or NOT NULL WITH DEFAULT, the column allows NULL values.

LIBNAME Statement Data Conversions

The following table shows the default SAS System variable formats that the DB2 engine assigns to DB2 data types during input operations.

LIBNAME Statement: Default SAS Formats for DB2 Data Types
DB2 Column Type Default SAS Format
CHAR(n) $n. (n<=254)
VARCHAR(n) $n.


$255. (n>255)

LONG VARCHAR $n.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC $n.( n<=127)

$127. (n>127)

INTEGER m.n
SMALLINT m.n
DECIMAL(m,n) m.n
FLOAT none
NUMERIC(m,n) m.n
DATE DATE9.
TIME TIME8.
DATETIME DATETIME30.6

The following table shows the default DB2 data types that are assigned to SAS variable formats during output operations.

LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats
SAS Variable Format DB2 Data Type
$w., $CHARw., $VARYINGw., $HEXw. CHARACTER
any date format DATE
any time format TIME
any datetime format TIMESTAMP
all other numeric formats FLOAT

ACCESS Procedure Data Conversions

The following table shows the default SAS System variable formats that the ACCESS procedure assigns to DB2 data types.

ACCESS Procedure: Default SAS Formats for DB2 Data Types
DB2 Column Type Default SAS Format
CHAR(n) $n. (n<=199)
VARCHAR(n) $n.


$200. (n>200)

LONG VARCHAR $n.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC $n.( n<=127)

$127. (n>127)

INTEGER 11.0
SMALLINT 6.0
DECIMAL(m,n) m+2.s

for example, DEC(6,4) = 8.4

REAL E12.6
DOUBLE PRECISION E12.6
FLOAT(n) E12.6
FLOAT E12.6
NUMERIC(m,n) m.n
DATE DATE7.
TIME TIME8.
DATETIME DATETIME30.6

Note:   You can use the YEARCUTOFF= option to make your DATE7. dates comply with Year 2000 standards. For more information about this SAS system option, see SAS Language Reference: Dictionary.  

 

 

 

//MSSQL

select ISNULL(max(WR_NO),0) as u_id from vote_Doc

//DB2

EX1)
select CASE WHEN MAX(WR_NO) IS NULL  
            THEN 0                        
            ELSE MAX(WR_NO)  END as u_id
from vote_Doc 

EX2)
select COALESCE(MAX(WR_NO),0)  as u_id
  from vote_Doc


값증가시
만약 MAX(u_id)에 1을 증가하려면

select CASE WHEN MAX(WR_NO) IS NULL  
            THEN 0                        
            ELSE MAX(WR_NO) + 1  END as u_id
from vote_Doc 

== 동일표현식 하나 더

select COALESCE(MAX(WR_NO) + 1,0)  as u_id
  from vote_Doc  

 
12345

+ Recent posts

티스토리툴바