Search code examples
sqlnetezzacase-when

Adding text to case when numeric result in SQL

I have a age variable that is calculated in decimals and I want to round it down and add text 'years' to the result. Desired output would be:

age         age_year
--------------------
0.0012      < 1 year
1.567        1 year 
6.230        6 year 
0.983       < 1 year

Currently, I have:

select 
case when (age < 1) then '< 1 year'
     when (age > 1) then floor(age) + 'years' 
     end as age_year

and I'm getting error of:

ERROR [HY000] ERROR:  Bad numeric input format 'years'

I can tell there is conflict of cell type, and it looks like age_year is still considered numeric. I think the solution would be casting cell type as string, but can't think of where / how to put that. Any suggestion would be appreciated!

Solution

  • String concatenation in Netezza uses ||:

    (case when age < 1 then '< 1 year'
      else floor(age) || ' years' 
 end) as age_year

    I removed the second condition, because else probably does what you want (unless age can be negative).