CODEWITHSUNDEEP
sqloracle-databasesubquerynvl
karthick karmegam
karthick karmegam

Reputation: 13

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?

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

The ISO/ANSI standard function for this is COALESCE():

SELECT a.artist_id AS ArtistID,
       a.name AS Song,
       COALESCE(ar.name, '*') AS artistname,
       COALESCE(s.name, '*') AS Albumname
FROM album a LEFT OUTER JOIN
     song s
     ON a.id = d.album_id LEFT OUTER JOIN
     artist ar
     ON ar.id = a.artist_id;

Notice that I changed the table aliases so they are abbreviations for the table names.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142743

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

Upvotes: 0

Related Questions