sqloracle-databasesubquerynvl

Oracle SQL report generation without null


My query:

SELECT s.artist_id AS ArtistID,
       s.name AS Song,
       at.name AS artistname,
       a.name AS Albumname
  FROM album s
       LEFT OUTER JOIN song a ON s.id = a.album_id
       LEFT OUTER JOIN artist at ON at.id = s.artist_id;

null in song and album table

for above query after joining the tables there is null in report (demo result)

Actual result

A B     
1 2                                                        
3 

Expected result

A B                                   
1 2                                                  
3 *  

I want to print the above result in excel instead of null it should be replaced with any character. Could you pls help me how to do it?


Solution

  • One option is to use the NVL function, e.g.

    nvl(a.name, '*') as albumname
    

    Or, DECODE might help:

    decode(a.name, null, '*', a.name) as albumname
    

    Or CASE:

    case when a.name is null then '*'
         else a.name
    end as albumname