Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Ottawa
    Posts
    21

    Unanswered: Converting decimal(4) to a decimal (4,2) value

    Hi, could someone give an example as to how I would go about converting a decimal (4) to decimal(4,2)?

    I've tried this
    INSERT INTO TEMPORARY_TABLE
    SELECT
    CUS_WLOC_SRC_CSN
    ,CUS_WLOC_CD
    ,REF_RT_TCSN
    ,REF_RT_INSR_DTTM
    ,REF_RT_EDTTM
    ,REF_RT_XDTTM
    ,SUBSTR(DIGITS(REF_RT_PCT),1,4)||'.'||'00'
    ,REF_RT_OVRD_DUR
    FROM MAIN_TABLE
    ;

    But it gives me incompatible errors.

    I'm working on a DB2 UDB version 7.1 on Windows.

    Thanks.
    Mbourd25

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should be able to just pass the decimal(4). It should be converted automatically.

    Your SUBSTR use is changing it into a string which is you compatible issue.

    This should work:

    INSERT INTO TEMPORARY_TABLE
    SELECT
    CUS_WLOC_SRC_CSN
    ,CUS_WLOC_CD
    ,REF_RT_TCSN
    ,REF_RT_INSR_DTTM
    ,REF_RT_EDTTM
    ,REF_RT_XDTTM
    ,REF_RT_PCT,
    ,REF_RT_OVRD_DUR
    FROM MAIN_TABLE
    ;


    Andy

  3. #3
    Join Date
    Mar 2004
    Location
    Ottawa
    Posts
    21
    Hi ARWinner, I just tried your way and I got this message.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0413N Overflow occurred during numeric data type conversion.
    SQLSTATE=22003

    Any ideas what could be the problem?
    Mbourd25

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the DDL for: TEMPORARY_TABLE and MAIN_TABLE?

    Andy

  5. #5
    Join Date
    Mar 2004
    Location
    Ottawa
    Posts
    21
    Here is the TEMPORARY TABLE

    CREATE TABLE TEMPORARY_TABLE (
    CUS_WLOC_SRC_CSN SMALLINT NOT NULL,
    CUS_WLOC_CD CHAR(4) NOT NULL,
    REF_RT_TCSN SMALLINT NOT NULL,
    REF_RT_INSR_DTTM TIMESTAMP NOT NULL,
    REF_RT_EDTTM TIMESTAMP NOT NULL,
    REF_RT_XDTTM TIMESTAMP NOT NULL,
    --> REF_RT_PCT DECIMAL(4,2), <-- This is the only difference
    REF_RT_OVRD_DUR DECIMAL(2),
    AUD_UID CHAR(8) NOT NULL,
    AUD_ACTN_DTTM TIMESTAMP NOT NULL,
    AUD_ACTN_CD CHAR(1) NOT NULL,
    AUD_PGM_ID CHAR(15),
    CONSTRAINT XMTRFRTP_TMP
    PRIMARY KEY (CUS_WLOC_SRC_CSN, CUS_WLOC_CD, REF_RT_TCSN,
    REF_RT_INSR_DTTM)
    );

    MAIN_TABLE

    CREATE TABLE MAIN_TABLE (
    CUS_WLOC_SRC_CSN SMALLINT NOT NULL,
    CUS_WLOC_CD CHAR(4) NOT NULL,
    REF_RT_TCSN SMALLINT NOT NULL,
    REF_RT_INSR_DTTM TIMESTAMP NOT NULL,
    REF_RT_EDTTM TIMESTAMP NOT NULL,
    REF_RT_XDTTM TIMESTAMP NOT NULL,
    REF_RT_PCT DECIMAL(4),
    REF_RT_OVRD_DUR DECIMAL(2),
    AUD_UID CHAR(8) NOT NULL,
    AUD_ACTN_DTTM TIMESTAMP NOT NULL,
    AUD_ACTN_CD CHAR(1) NOT NULL,
    AUD_PGM_ID CHAR(15),
    CONSTRAINT XMTRFRTP_TMP
    PRIMARY KEY (CUS_WLOC_SRC_CSN, CUS_WLOC_CD, REF_RT_TCSN,
    REF_RT_INSR_DTTM)
    );

    Thanks for your help.
    Mbourd25

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think your problem is now this. Your source is decimal(4). This means that the number can be 1->9999. Your destination is decimal(4,2) which means that you number can be 1-99.99. Any value greater than 99.99 on the source will give you the overflow error. You nead to make your target decimal(6.2) for it to work.

    Andy

  7. #7
    Join Date
    Mar 2004
    Location
    Ottawa
    Posts
    21
    Hi Andy, you are right, I changed it to decimal 6,2 and it worked like a charm.

    Thanks for your help.
    Mbourd25

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •