coalesce and date

[rrrkrishnan]

With tbl1 being empty, can someone help me understand why col2 is null.

SELECT MAX(extr_prd_end_dt) AS col1,
COALESCE(MAX(extr_prd_end_dt), CURRENT_DATE-EXTRACT DAY FROM CURRENT_DATE)) AS col2,
COALESCE(MAX(extr_prd_end_dt), CURRENT_DATE) AS col3,
COALESCE(NULL, CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)) AS col4,
CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) AS col5
FROM tbl1;

col1 col2 col3 col4 col5
? ? 2003-10-06 2003-09-30 2003-09-30

Thanks
Krishnan

 

[Liam1]

Krishnan,
COALESCE can be re-written as:
CASE WHEN TBL1.MAX(extr_prd_end_dt) is NOT NULL then BL1.MAX(extr_prd_end_dt)
ELSE
CURRENT_DATE-EXTRACT DAY FROM CURRENT_DATE))

As the date is null, it cannot have an aggregate function performed against it.

Liam1

 

[dnoeth]

This is one of the mysteries of SQL ;-)

Maybe this article helps a bit:

http://www.intelligententerprise.com/021008/516celko1_1.shtml

Dieter

 

[rrrkrishnan]

Liam1 & Dieter

Thanks for your responses.

I had already tried using CASE instead of COALESCE.
Here is the query and the result, and I am mystified to see a value for col7 but NULL for col6.

SELECT MAX(extr_prd_end_dt) as col1,
COALESCE(max(extr_prd_end_dt), CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)) as col2,
COALESCE(max(extr_prd_end_dt), CURRENT_DATE) as col3,
COALESCE(null, CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)) as col4,
CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) as col5,
CASE WHEN MAX(extr_prd_end_dt) is NOT NULL then MAX(extr_prd_end_dt) ELSE CURRENT_DATE-EXTRACT (DAY FROM CURRENT_DATE) end as col6,
CASE WHEN MAX(extr_prd_end_dt) is NOT NULL then MAX(extr_prd_end_dt) ELSE CURRENT_DATE end as col7;

col1 col2 col3 col4 col5
col6 col7
? ? 2003-10-07 2003-09-30 2003-09-30
? 2003-10-07

Dieter

I was able to follow the article, but all that information is not helping me solve this issue, or am I missing something.

Thanks
krishnan

 

[rrrkrishnan]

I have a workaround, but still I don't see any reason why I should have to complicate it.

SELECT COALESCE(MAX(extr_prd_end_dt + 1), DATE'1900-01-01') (date, format 'yyyy-mm-dd') as col1,
CAST((COALESCE(ADD_MONTHS(MAX(extr_prd_end_dt),1), CURRENT_DATE)) - (CASE WHEN MAX(extr_prd_end_dt) IS NULL THEN EXTRACT(DAY FROM CURRENT_DATE)ELSE 0 END) AS FORMAT 'YYYY-MM-DD') as col2
FROM tbl1;

col1 col2
1900-01-01 2003-09-30

 

[dnoeth]

Now i see what you mean ;-)

This behaviour is inconsistent and looks like a bug, try to open an incident.

Dieter

 

[rrrkrishnan]

Dieter

Can you please let me know as to how I open an incident.

Thanks
krishnan