oracle-databaseselectoracle-sqldevelopernvl

How to replace NVL function in a sql query

I need to replace an NVL function into a select

for example

NVL (MAX(EX.ID_EXAMPLE),0)

How can I replace this into my query

Thanks a lot

Solution 
  • NVL (MAX(EX.ID_EXAMPLE),0)

    can be replaced with CASE..WHEN as following:

    CASE WHEN 
MAX(EX.ID_EXAMPLE) IS NOT NULL THEN 
MAX(EX.ID_EXAMPLE)
ELSE 0 
END

    and better way is to use a subquery in which MAX is calculated and in outer query use alias in CASE..WHEN so that MAX is not calculated twice.

    SELECT CASE WHEN
MAX_ID_EXAMPLE IS NULL THEN MAX_ID_EXAMPLE 
ELSE 0 
END
...
FROM (SELECT MAX(EX.ID_EXAMPLE) AS MAX_ID_EXAMPLE 
...
FROM ...)

    Cheers!!