Oracle SQL report generation without null

Question

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?

Gordon Linoff · Accepted Answer · 2018-12-11 11:38:04Z

2

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.

answered Dec 11, 2018 at 11:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2018-12-11 11:05:01Z

0

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

answered Dec 11, 2018 at 11:05

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

1 Comment

jarlh Over a year ago

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

Collectives™ on Stack Overflow

Oracle SQL report generation without null

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related