rename NULL column outputs

I have this view:
CREATE VIEW CautionDescriptions AS
SELECT TOP 100 PERCENT
Oils.OilName AS [Oil name], Cautions.Description AS Caution
FROM Cautions
INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
ORDER BY Oils.OilName
I want to return all rows even those with NULLs in the description key. Is
there a way to replace the <NULL>s in the description column with more
attractive text?
like
if OilCautions.OilID ISNULL 'No description available'
endif
Where could I put this in the SELECT or FROM clause?SELECT COALESCE(column_name, 'No description available')
You can also use ISNULL. Funny, I just wrote an article about this earlier
today:
http://www.aspfaq.com/2532
http://www.aspfaq.com/
(Reverse address to reply.)
"Patrick" <psully@.nospam.eatel.net> wrote in message
news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
> I have this view:
> CREATE VIEW CautionDescriptions AS
> SELECT TOP 100 PERCENT
> Oils.OilName AS [Oil name], Cautions.Description AS Caution
> FROM Cautions
> INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
> RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
> ORDER BY Oils.OilName
> I want to return all rows even those with NULLs in the description key. Is
> there a way to replace the <NULL>s in the description column with more
> attractive text?
> like
> if OilCautions.OilID ISNULL 'No description available'
> endif
> Where could I put this in the SELECT or FROM clause?
>
>|||Thanks Aaron! That was a fast answer. I never heard of coalesce. I am going
to read your article too, thanks again!
BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
Programming. The book name is certainly a misnomer, the book is mostly about
using Enterprise Manager.
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||Read your article. **** rating!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||> BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
> Programming. The book name is certainly a misnomer, the book is mostly
about
> using Enterprise Manager.
Then you might want to read this article too:
http://www.aspfaq.com/2455
:-)
PS I suggest creating your views from scratch, as opposed to letting
Enterprise Manager write the code for you ... because of the limitations in
the view designer, as well as its funky quirks like putting TOP 100 PERCENT
and ORDER BY in views for some reason.|||Oops, I think I hit reply too soon (may be an empty reply here soon).
Anyway, that was an interesting and informative article. I've taken a few
SQL Server courses, and they all start of with EM. But Step by Step does
cover the QA, too. I remember vaguely when I learned how to use it a little
last year, I was impressed a lot by QA. I mainy use EM for experimenting
with joins really fast, and if it works, copy the script and edit it in QA.
When are you going to turn all these "tips" into a book? lol, thanks!
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ur6ou3YAFHA.4072@.TK2MSFTNGP10.phx.gbl...
2000
> about
> Then you might want to read this article too:
> http://www.aspfaq.com/2455
> :-)
> PS I suggest creating your views from scratch, as opposed to letting
> Enterprise Manager write the code for you ... because of the limitations
in
> the view designer, as well as its funky quirks like putting TOP 100
PERCENT
> and ORDER BY in views for some reason.
>