Sorting with NULL

[Sharif Islam]

I used SET
CONCAT_NULL_YIE LDS_NULL OFF do ignore NULL in concatation. I have two
fields:
chapterauthor




authors

Here's my query:
SELECT ([chapterauthor] + [authors]) AS CT FROM Items ORDER BY CT


Both Authors and ChapterAuthor can be null. I am trying to figure out if
I can ignore the null value when ChapterAuthor is null.

Sample data/result:
Appadurai, Arjun #chapter author is null





Appadurai, ArjunFardon, Richard (ed.) #
(chapterauthor+ authors)

I would like the output to be like this:




Appadurai, ArjunFardon, Richard (ed.) #
(chapterauthor+ authors)
Appadurai, Arjun #chapter author is null


I guess I can write a stored procedure with if/else, but I was wondering
If I can do this with a simple query. Thanks.

[--CELKO--]

Re: Sorting with NULL

>I used SET CONCAT_NULL_YIE LDS_NULL OFF do ignore NULL in concatation. <<

Why? Do you always violate ANSI/ISO standards in your code? Why not
write portable code with COALESCE()? Or with a DEFAULT and a CHECK()
constraint?"

Most bad DML comes from bad DDL.