why coalesce around time interval calculation does not work ?

Database
Ike
Teradata Employee

why coalesce around time interval calculation does not work ?

Hi guys,

does anybody know, why coalesce around time interval calculation does not work ?

This works ok :

cast( (dr.CDS_RRRRMMDD_Trigger - P.party_indiv_birth_dt) as Interval YEAR) as Age

This doesn't :

coalesce( cast( (dr.CDS_RRRRMMDD_Trigger - P.party_indiv_birth_dt) as Interval YEAR) ,-1) as Age

 

I'd like to keep it as 1 line.

 

Thx

Ivan

Tags (2)
1 REPLY
Senior Apprentice

Re: why coalesce around time interval calculation does not work ?

Hi Ivan,

the COALESCE fails because it's you can't mix both INTERVAL and INTEGER.

I doubt the first cast returns the correct age, you get the number of days between start and end and then you cast this as an interval year. This should result in an overlfow error if the difference is greater than 99 days. 

It should be 

cast( (dr.CDS_RRRRMMDD_Trigger - P.party_indiv_birth_dt YEAR (4)) as  SMALLINT) 

and then the coalesce is working, too.

And check if the result of that date-date year is really what you need, at least it's not the result a human being age would expect.

I use following functions for age calculation:

/***
age in years, two variations regarding leap years:
is the duration between 2012-02-29 and 2013-02-28 a full year?
***/

/***
age in years,
duration between 2012-02-29 and 2013-02-28 is a full year:

SELECT age(DATE '2013-02-28', DATE '2012-02-29');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

age(2013-02-28,2012-02-29)
--------------------------
1
***/
REPLACE FUNCTION age(d1 DATE, d2 DATE)
RETURNS INT
SPECIFIC age_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
MONTHS_BETWEEN(d1,d2)/12 (INT)
;

/***
age in years,
duration between 2012-02-29 and 2013-02-28 is not a full year:

SELECT age2(DATE '2013-02-28', DATE '2012-02-29');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

age2(2013-02-28,2012-02-29)
--------------------------
0
***/
REPLACE FUNCTION age2(d1 DATE, d2 DATE)
RETURNS INT
SPECIFIC age2_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
((d1 (INT)) - (d2 (INT))) / 10000
;