CODEWITHSUNDEEP
sqlamazon-redshift
Mayur Randive
Mayur Randive

Reputation: 643

Multiplication in redshift

I am new to Redshift, trying to do multiplication in my SQL as mentioned below 

Case  When COALESCE(height * width * length * weight, 0) = 0 then ...

getting below error message,

Invalid operation: Resulting scale overflows maximum precision Details:

Can someone please guid me.

Upvotes: 0

Views: 5173

Answers (1)

ittus
ittus

Reputation: 22393

As Redshift document:

precision

The total number of significant digits in the whole value: the number of digits on both sides of the decimal point. For example, the number 48.2891 has a precision of 6 and a scale of 4. The default precision, if not specified, is 18. The maximum precision is 38.

So I think it depends on what's current precision of your data type. One way to work around is casting:

Case When COALESCE(height::decimal(8,3) * width::decimal(8,3) * length::decimal(8,3) * weight::decimal(8,3), 0) = 0 then ...

Upvotes: 2

Related Questions