Netezza Sql case statement with different formats

Debbie Cooper
Debbie Cooper used Ask the Experts™
on
I am calculating age from a TIMESTAMP Birth_date.  In the case of children under the age of 1, I would like to have the age show up as .3 if the child is born, for example, In August, 2019.  But for all others I would like the age to show up as an integer.  I am using code that someone gave me:

select id, birth_date, age from (
select id, birth_date,
CASE WHEN MONTHS_BETWEEN(NOW(),(BIRTH_DATE))<12 THEN
CAST(SUBSTR(MONTHS_BETWEEN(NOW(),(BIRTH_DATE))/12,1,3) AS NUMERIC(3,2)) --Infants
    ELSE CAST((TRUNC(MONTHS_BETWEEN(NOW(),(BIRTH_DATE))/12,0)) AS NUMERIC)  
--Adults
END AS age
 from tmp1 order by id, age)x

The problem is that I want two different formats, one for ages >=12 months and another for ages <12 months but no matter what I try I get one format for all.  So, for example, in the code above I get this:
ID                       BIRTH_DATE                             AGE
1                           8/30/2019 12:00:00 AM            0.30
2                      2/2/1918 12:00:00 AM                101.00
3                      9/19/1937 12:00:00 AM          82.00
4                      8/26/1918 12:00:00 AM        101.00

How can I get the ages above 12 months to show up as integer and those below 12 months to show up in decimal format?  I would think the case statement would carry the different formats but it does not.  I want it to look like this:

ID                       BIRTH_DATE                             AGE
1                           8/30/2019 12:00:00 AM            0.30
2                      2/2/1918 12:00:00 AM                101
3                      9/19/1937 12:00:00 AM          82
4                      8/26/1918 12:00:00 AM        101

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
You can have only one data type. So it's either NUMERIC or INTEGER for all values:

SELECT   id ,
         birth_date ,
         CAST(CASE WHEN MONTHS_BETWEEN(NOW(), BIRTH_DATE) < 12 THEN 0.3
                   ELSE MONTHS_BETWEEN(NOW(), BIRTH_DATE) / 12
              END AS NUMERIC(4, 1)) AS age
FROM     tmp1
ORDER BY id ,
         MONTHS_BETWEEN(NOW(), BIRTH_DATE);

Open in new window

TRUNC() is only necessary, when Netezza does an implicit cast to floating point data type or numeric one. Normally in SQL a division of INTEGER / INTEGER yields an INTEGER.
Does the ORDER BY make sense? I would expect ID to be unique, so that the second order criteria seems redundant.

I would think the case statement would carry the different formats but it does not.
That's the point. SQL works with data types. What you see is the result of a tool visualizing data, aka formatting. Formatting is normally front-end task, where you e.g. set the format when you print a value.
Here you have the case of using different formats for different values. This requires a conditional. If you really want to do it in SQL, then this means, that you need to return a string. But this will also mean, that you need to know the users locales while formatting the values.

E.g.

SELECT   id ,
         birth_date ,
         CASE WHEN MONTHS_BETWEEN(NOW(), BIRTH_DATE) < 12 THEN '0.3'
              ELSE CAST(MONTHS_BETWEEN(NOW(), BIRTH_DATE) / 12 AS VARCHAR(3))
         END AS ageText
FROM     tmp1
ORDER BY id ,
         MONTHS_BETWEEN(NOW(), BIRTH_DATE);

Open in new window

But this also means, that sorting or doing client-side calculus will be pretty hard.

I would set the age either to 0 or 1 but would also have an additional flag I(nfant)/N(ormal). Thus you can do precise calculus as well as filtering in SQL.

Calculating the current age is in some use-case like statistics by age buckets. Here you would normally calculate the age according to an interval start or end. Thus the age at the being/end of the current month, quarter or year.
Debbie CooperConsultant

Author

Commented:
Sorry for being so late to reply.  I ended up setting the age to 1 when MONTHS_BETWEEN(NOW(),BIRTH_DATE) < 12 but I hadn't thought about adding an I(nfant)/N(ormal) flag.  I like that idea.  Thanks so much for the explanation.
Debbie CooperConsultant

Author

Commented:
Hey st5an, I ended up setting the age to 1 when MONTHS_BETWEEN(NOW(),BIRTH_DATE) < 12 as you suggested.  Your explanation of how data types are handled was very informative and I'm keeping that for reference

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial