In my current release, my team had an issue with using NVL function in Oracle. We've resolved it now but the root cause is still not clear. we're using Oracle 10g XE in our local machines and there is a full 10g Enterprise Edition installed on the server.
There was a statement like this -
SELECT DISTINCT reg.Document_ID
,reg.Effective_Date
,nvl(reg.Expiration_Date,to_date('31/12/9999','DD/MM/YYYY')) Expiration_Date
,reg.Country_Code
FROM S1Reg reg
WHERE reg.Event_Code = 'NRES' AND
(
NVL((SELECT COUNT(*) FROM S1Reg WHERE STATUS_CODE = 'AC' AND REG.DOCUMENT_ID=DOCUMENT_ID GROUP BY DOCUMENT_ID),0) = 0
OR reg.STATUS_CODE IS NULL
OR (reg.EVENT_TYPE_CODE = 'REG' AND reg.STATUS_CODE = 'AC')
)
See the code in bold. There is a NVL function on count (*) that has a group by clause. Count never returns null, however, due to the group by, there are zero rows returned in some cases. This was taken as NULL value and therefore a NVL function was wrapped around it. It seemed to work fine in our XE instances, however it did not work on the Oracle server instance. I've got this code changed now, however, it is not clear why it worked on XE and not Oracle.
Any pointers will be helpful.