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.

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

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?

Can you post the DDL for: TEMPORARY_TABLE and MAIN_TABLE?

Andy

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.

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

Hi Andy, you are right, I changed it to decimal 6,2 and it worked like a charm.

Thanks for your help.