SQL/SAP HANA SQL DATATYPES

Question

I need to know how can i get an 'E' to appear for each of the columns when the sum of all these columns = 0. I've tried case when <...> then 'E' but i keep getting "SAP DBTech JDBC: [339]: invalid number: not a valid number string 'E'"

i figure its because of the data types, the column seems to be in DOUBLE or INT Please help. Thanks

ROUND((EBD."Disagg Charge"*ID."perc_airConditioning"),2) AS "CB_Cooling($)",
ROUND((EBD."Disagg Charge"*ID."perc_refrigeration"),2) AS "CB_Fridges & Freezers($)",
ROUND((EBD."Disagg Charge"*ID."perc_waterHeating"),2) AS "CB_Electric Hot Water($)",
ROUND((EBD."Disagg Charge"*ID."perc_cooking"),2) AS "CB_Cooking($)",
ROUND((EBD."Disagg Charge"*ID."perc_laundry"),2) AS "CB_Laundry & Dishwasher($)",
ROUND((EBD."Disagg Charge"*ID."perc_lighting"),2) AS "CB_Lighting($)",
ROUND((EBD."Disagg Charge"*ID."perc_entertainment"),2) AS "CB_Home Entertainment($)",
ROUND((EBD."Disagg Charge"*ID."perc_pool"),2) AS "CB_Pool Pump($)",

Please show what you've tried so far – Roberto Jun 14 '17 at 21:16 — Roberto, Jun 14 '17 at 21:16

score 0 · Answer 1 · answered Jun 09 '17 at 02:19

The problem you face here is that the output data type needs to be fixed when you write your SQL statement. SQL is statically typed, so the result cannot be DOUBLE for non-zero values and STRING for zero values.

So, if you want to have the described behaviour, making the output column a STRING type is the only option you have here. One consequence of this is that whatever client application reads this result, will not be able to understand that this column is mainly a DOUBLE data type (e.g. formatting becomes a lot harder and further computation likely will require another data type change). If you're comfortable with this, then go ahead...

Here's how such code can look like in SAP HANA:

with some_data as (
                select 'A' as add_group, 1.2 as mynumber from dummy
    union all   select 'A' as add_group, 4.523 as mynumber from dummy
    union all   select 'A' as add_group, 3.23 as mynumber from dummy
    union all   select 'B' as add_group, 3.5 as mynumber from dummy
    union all   select 'B' as add_group, -1.75 as mynumber from dummy
    union all   select 'B' as add_group, -1.75 as mynumber from dummy
)
select 
    add_group, sum(mynumber),
    case 
        when (sum (mynumber) = 0.0)
            then 'E'
        else 
            to_varchar( sum (mynumber))
    end as err_sum
from 
    some_data
group by add_group;


ADD_GROUP   SUM(MYNUMBER)   ERR_SUM
A           8.953           8.953
B           0               E

SQL/SAP HANA SQL DATATYPES

1 Answers1