Multiplication of Decimals

Database
Enthusiast

Multiplication of Decimals

I am multiplying too decimals. I am running below query in two different servers. In one system DBS Parameter - MaxDecimal is set as 15 whereas another has DBS Parameter - MaxDecimal as 38. Below JOIN query is failing in second server (MaxDecimal -38) whereas it running fine in first server (MaxDecimal - 15). I am not able to get any explanation in any document.

Error encountered in second server - 3754:  Precision error in FLOAT type constant or during implicit conversions. 

Query -

CREATE VOLATILE TABLE X
(col VARCHAR(255)) ON COMMIT PRESERVE ROWS;

DROP TABLE Test_Decimal;
CREATE VOLATILE TABLE Test_Decimal
(col DECIMAL (12,0)) ON COMMIT PRESERVE ROWS;

SEL * FROM Test_Decimal A
LEFT OUTER JOIN X
ON X.Col = CAST(-1 AS DECIMAL (5,0))* A.col
;
Gyan
Tags (1)
2 REPLIES
Enthusiast

Re: Multiplication of Decimals

Hi,

You have created the col in table X as varchar and while comparing this number to a decimal it will try for an implicit conversion into the default maxdecimal 38 percision. Real numbers are representable to 15 digits of precision in the database, so the percision greater than 15 will cause error.

you can try the following:

CREATE VOLATILE TABLE X
(col VARCHAR(255)) ON COMMIT PRESERVE ROWS;

DROP TABLE Test_Decimal;
CREATE VOLATILE TABLE Test_Decimal
(col DECIMAL (12,0)) ON COMMIT PRESERVE ROWS;

SEL * FROM Test_Decimal A
LEFT OUTER JOIN X
ON CAST(X.Col AS DECIMAL(5,0)) = CAST(-1 AS DECIMAL (5,0))* A.col
;
Khurram
Enthusiast

Re: Multiplication of Decimals

Hi Khurram,

I have tried below two scenarios on (v12 and V14.10 both).

CREATE VOLATILE TABLE X
(col VARCHAR(255)) ON COMMIT PRESERVE ROWS;
CREATE VOLATILE TABLE Test_Decimal
(col DECIMAL (16,0)) ON COMMIT PRESERVE ROWS;

SEL * FROM Test_Decimal A
LEFT OUTER JOIN X
ON X.Col = A.col
;
--SELECT Successful.

CREATE VOLATILE TABLE X
(col VARCHAR(255)) ON COMMIT PRESERVE ROWS;
CREATE VOLATILE TABLE Test_Decimal
(col DECIMAL (17,0)) ON COMMIT PRESERVE ROWS;

SEL * FROM Test_Decimal A
LEFT OUTER JOIN X
ON X.Col = A.col
;
--SELECT Failed. 3754: Precision error in FLOAT type constant or during implicit conversions.

If Real numbers are representable to 15 digits, then I think first one should also fail. Please confirm. 

Sorry for wiered questions, but I want to be confident before I explain this to somebody.

Gyan