Using Nvl for Multiple Columns - Oracle Sql

Using NVL for multiple columns - Oracle SQL

You could nest NVL:

 NVL(a, NVL(b, NVL(c, d))

But even better, use the SQL-standard COALESCE, which does take multiple arguments and also works on non-Oracle systems:

COALESCE(a, b, c, d)

Multiple NVL() alternative - first not null parameter

Perhaps you are looking for COALESCE()?

Note that COALESCE() is supported on almost all khown databases: Oracle, PostgreSQL, MySQL, MSSQL, SQLite.

How to use NVL in PL/SQL for Date columns?

I wouldn't use nvl for this, partly because it would exclude column values that are null. I would test explicitly for null arguments; something like:

OPEN R1 FOR
    SELECT * FROM USER
    WHERE (UserID IS NULL OR id = UserID)
    AND (Name IS NULL OR LASTNAME = Name OR FIRSTNAME = Name)
    AND (FromDate IS NULL OR releaseddate >= TO_DATE(FromDate, 'dd/mm/rrrr'))
    AND (ToDate IS NULL OR releaseddate <= TO_DATE(ToDate, 'dd/mm/rrrr'))
    ORDER BY RELEASEDDATE DESC
    FETCH FIRST 100 ROWS ONLY;

This compares the releasedate column values as a date, rather than converting it to a string; instead the string arguments are converted to dates to match the column data type, which is more efficient. It would be better to have the procedure arguments declared as dates, and make the caller provide a valid value.

Use NVL on a specified column while still returning all other columns in SQL

There is no way to select * and interrupt the *, so you either have to spell out each column or alias long/lat as separate names and select them twice...

SELECT A.*
     , case when Latitude is not null 
             and Longitude is null then 0 else longitude end as LongitudeA
     , case when Latitude is null 
             and Longitude is not null then 0 else latitude end as LatitudeA 
FROM table

Now this is oracle so spelling out all the fields from a table would be as easy as...

SELECT wm_concat(column_name) 
from All_tab_Cols 
where table_Name = 'TABLE' 
  and Owner = 'SCHEMA' 
  and column_name not in ('LATITUDE','LONGITUDE')

Then copy paste the results over the A.* above and no typing required for listing out all the fields (assuming < 4000 characters)

Or, you could use List_Agg() if your version of oracle supports it and provide better ordering by column_ID or column_name.

Now given this you could write the SQL to be dynamic SQL but that seems to be overkill.

---

Related Topics