NVL vs COALESCE for date field

General
Enthusiast

NVL vs COALESCE for date field

I am working on converting this Oracle SQL (that runs fine) to Teradata

 

AND ED.LASTWRITTEN = NVL((SELECT MAX(LASTWRITTEN) FROM CURAPD01.EVIDENCEDESCRIPTOR
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
AND EFFECTIVEFROM IS NOT NULL
),(SELECT MAX(LASTWRITTEN) FROM CURAPD01.EVIDENCEDESCRIPTOR
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
))

GROUP BY ED.CASEID,

 

I don't think date fields work with NVL so I am trying to use COALESCE and converting the LASTWRITTEN field from date to VARCHAR.

However, this SQL is bombing out with 3706 Syntax errors. It could just be that i don't have the ')'s lined up correctly. Any suggestions would be greatly appreciated.

 

AND cast(ED.LASTWRITTEN as varchar(20)) = select MAX((COALESCE(CAST(LASTWRITTEN as varchar(20))) FROM MNsureViews.EVIDENCEDESCRIPTORV
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
AND EFFECTIVEFROM IS NOT NULL
),(SELECT MAX((COALESCE((CAST(LASTWRITTEN as varchar(20))) FROM MNsureViews.EVIDENCEDESCRIPTORV
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
))
GROUP BY ED.CASEID,


Accepted Solutions
Junior Contributor

Re: NVL vs COALESCE for date field

NVL doesn't work with dates? Strange.

 

You only need to replace the NVL keyword with COALESCE:

 

AND ED.LASTWRITTEN = COALESCE((SELECT MAX(LASTWRITTEN) FROM CURAPD01.EVIDENCEDESCRIPTOR
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
AND EFFECTIVEFROM IS NOT NULL
),(SELECT MAX(LASTWRITTEN) FROM CURAPD01.EVIDENCEDESCRIPTOR
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
))
GROUP BY ED.CASEID,

But the logic is too complicated and can be simplified to:

AND ED.LASTWRITTEN = 
 ( SELECT Coalesce(Max(CASE WHEN EFFECTIVEFROM IS NOT NULL THEN LASTWRITTEN END), Max(LASTWRITTEN))
   FROM CURAPD01.EVIDENCEDESCRIPTOR
   WHERE CASEID=ED.CASEID
   AND EVIDENCETYPE=ED.EVIDENCETYPE
   AND PARTICIPANTID = ED.PARTICIPANTID
   AND STATUSCODE = ED.STATUSCODE
 )
GROUP BY ED.CASEID,

Additionally Scalar Subqueries are usually less efficient this can probably be replaced by a Left Join or OLAP-functions, too

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: NVL vs COALESCE for date field

NVL doesn't work with dates? Strange.

 

You only need to replace the NVL keyword with COALESCE:

 

AND ED.LASTWRITTEN = COALESCE((SELECT MAX(LASTWRITTEN) FROM CURAPD01.EVIDENCEDESCRIPTOR
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
AND EFFECTIVEFROM IS NOT NULL
),(SELECT MAX(LASTWRITTEN) FROM CURAPD01.EVIDENCEDESCRIPTOR
WHERE CASEID=ED.CASEID
AND EVIDENCETYPE=ED.EVIDENCETYPE
AND PARTICIPANTID = ED.PARTICIPANTID
AND STATUSCODE = ED.STATUSCODE
))
GROUP BY ED.CASEID,

But the logic is too complicated and can be simplified to:

AND ED.LASTWRITTEN = 
 ( SELECT Coalesce(Max(CASE WHEN EFFECTIVEFROM IS NOT NULL THEN LASTWRITTEN END), Max(LASTWRITTEN))
   FROM CURAPD01.EVIDENCEDESCRIPTOR
   WHERE CASEID=ED.CASEID
   AND EVIDENCETYPE=ED.EVIDENCETYPE
   AND PARTICIPANTID = ED.PARTICIPANTID
   AND STATUSCODE = ED.STATUSCODE
 )
GROUP BY ED.CASEID,

Additionally Scalar Subqueries are usually less efficient this can probably be replaced by a Left Join or OLAP-functions, too

Enthusiast

Re: NVL vs COALESCE for date field

Thanks DNOETH !

I was out of the office last week, but tried your new code today and it works great!