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.
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