nvl is not a recognized built-in function name

Question

I am executing below query on SQL Server but getting error:

SELECT DISTINCT t1.p_id "Id",
(TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0)) "Year"

FROM 
   t1,
   t7,
   t9
WHERE 
   t9.ei_id(+)          = t7.e_id
AND (t7.e_student        = t1.p_id)
AND (t7.e_module         = t8.m_id)
AND (NVL(t9.ei_q18m06, t7.e_end) > '31-Jul-' | | (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0) + - 5))

Error:

'nvl' is not a recognized built-in function name. 

Any idea how this can be avoided?

Answer 1

NVL is Oracle syntax

The corresponding function in SQL server is ISNULL :

AND (ISNULL(t9.ei_q18m06, t7.e_end) > '31-Jul-' | | (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0) + - 5))

Even better, use COALESCE() which is standard ANSI and accepted by both Oracle and SQL server

AND (COALESCE(t9.ei_q18m06, t7.e_end) > '31-Jul-' | | (TO_CHAR("sysdate", 'YYYY') + least(SIGN(("sysdate" - to_date('01-Aug-' | | TO_CHAR("sysdate", 'YYYY'), 'DD-Mon-RRRR'))), 0) + - 5))

SQL NULL Functions