不带null的Oracle SQL报告生成

Question

我的查询：

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(演示结果(

实际结果

A B     
1 2                                                        
3 

预期结果

A B                                   
1 2                                                  
3 *  

我想在excel中打印以上结果，而不是空，它应该被替换为任何字符。你能帮我怎么做吗？

Answer 1

ISO/ANSI标准的函数是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;

请注意，我更改了表别名，使它们成为表名的缩写。

Answer 2

一个选项是使用NVL功能，例如

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

或者，DECODE可能会有所帮助：

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

或CASE:

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