Whenever I do a division of integers on Starburst, the results are always rounded either to 0 or 1. I would like the casting to automatically to set to decimals when this division is done. Additionally I need the DIV0 function to handle divisions by 0.
1 Like
TWO great questions in ONE post! Let’s see if I can help.
First up is the need for decimal places.
select 100 / 3 AS decimal_answer;
returns 3
select cast(100 AS double) / cast(3 AS double) as decimal_answer;
returns 33.333333333333336
select round(cast(100 AS double) / cast(3 AS double), 3) as decimal_answer;
returns 33.333
Second up is the divide by 0.
SELECT 100 / 0 AS div0_answer;
blows up!
SELECT COALESCE(TRY(100 / 0), 0) AS div0_answer;
returns 0
Please let me know if this doesn’t help.
1 Like
Just keep in mind that the result of a division by zero is NOT zero, but rather undefined, or even infinity. As such I suggest to be very careful about using that approach to return zero as the result.