You can translate the question and the replies:

Case statement on a column truncates the decimal values

Hi, We are using Denodo Platform version 6.0. When we select the column directly from the view I am getting the result with 2 decimal point, when I add case statement to check for null values on the same column, the decimals are truncated. Is there any configuration that we need to check to get the values as it is in table after the case statement? Ex: select case when cash_totalcy is null then 0 else cash_totalcy end as cash_totalcy_w_case, cash_totalcy as cash_totalcy from table_name where contract = 'ABC' Result: cash_totalcy_w_case cash_totalcy 14199 14199.09
user
03-05-2019 12:46:47 -0400

3 Answers

Hi, I tried the same scenario as you’ve mentioned with a sample data source, but I was not able to reproduce the same problem. What might be happening is, as you’re substituting 0 when the column has a NULL value, the query might be considering that new column (cash_totalcy_w_case) is an int column and that’s why it’s putting the other decimal values as int. To avoid such situation it would be a best practice to surround the 0 with a proper cast like cast(‘decimal’, 0) or write ...WHEN cash_totalcy IS NULL THEN 0.00... in your query Hope this helps!
Denodo Team
03-05-2019 20:25:19 -0400
Thank you for the Answer. When I try with case when cash_totalcy is null then 0.00 else cash_totalcy end as cash_totalcy_w_case, it gave decimal but rounded off to 1 digit. So the result was as below Result: cash_totalcy_w_case cash_totalcy 14199.1 14199.09 129.2 129.15 But when I try with case when cash_totalcy is null then cast('decimal', 0) else cash_totalcy end as cash_totalcy_w_case it gave the exact decimal value as in cash_totalcy. As of now, we are good to go with cast function. But would be curious to know why it was rounded off to 1 digit when we use it as 0.00 for null.
user
03-05-2019 21:09:55 -0400
Hi, Good to know that it is working with the cast function. As I mentioned before, I tried reproducing the same scenario and I was able to get all the decimals as expected. This scenario seems to be particular with the database you’re using. You can go to the database itself and try to query the case statements to see why it is behaving this way. Hope this helps!
Denodo Team
13-05-2019 13:33:07 -0400
You must sign in to add an answer. If you do not have an account, you can register here