All Forums Database
Ike 1 post Joined 02/08
26 Jan 2015
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

dnoeth 4628 posts Joined 11/04
26 Jan 2015

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
;

 

Dieter

You must sign in to leave a comment.