Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Select statement


SQL Select statement

Author
Message
VSSGeorge
VSSGeorge
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 1406

SELECT   
C.containerCode + ':' + C.containerName as display,
C.containerCode AS code
FROM Containers C
INNER JOIN ContainersTypes CT ON
   CT.containerTypeSqlId = C.containerTypeSqlId AND
   CT.containerTypeIncId = C.containerTypeIncId
WHERE   C.isDeleted = 0
ORDER BY display




Hi

I have a query like this for populating a report parameter combo box in my SRRS report. But when the
C.containerName in the ‘display’ column is NULL, the ‘display’ column returns a NULL value.
Also the ‘display’column values with NULL will come first in the result set instead of the result ordering by display column.
How can I avoid this?
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 11172
If you concatenate strings together and one of them contains NULL then the result will always be NULL (unless you've played with the default CONCAT_NULL_YIELDS_NULL setting).
Have a look at ISNULL, to replace a NULL value with something more useful.

Steve Hall
Linkedin
Blog Site
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6970 Visits: 8839
First question to pose is: Shoud that column have nulls ?
Second question to pose is: wouldn't a default empty varchar do the same trick ? If so modify the data model so the column is Not-Nullable and put an empty string in all occurences that are nulled.


If you cannot avoid NULLs, your applications will have to deal with that every time !



your TSQL options are
Isnull( yourcolumn, 'replacementvalue' )
Isnull( yourcolumn, othercolumn ) -- <-- may still return NULL if othercolumn is also nullable
Coalesce( yourcolumn, othercolumn, ..., 'replacementvalue')



check books online for details !

When using concatenation, keep in mind you also need to translate the columns to a common data type.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
VSSGeorge
VSSGeorge
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 1406
Thanks everyone for the replies..
I changed my script like this:

SELECT   
ISNULL(C.containerCode, '') + ':' + ISNULL(C.containerName, '') as display,
C.containerCode AS code
FROM Containers C
INNER JOIN ContainersTypes CT ON
   CT.containerTypeSqlId = C.containerTypeSqlId AND
   CT.containerTypeIncId = C.containerTypeIncId
WHERE   C.isDeleted = 0
ORDER BY display


domainsource
domainsource
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 209
---- will return zzz for null and blank values if your data is varchar it will send it to the bottom of the list change to numerical values if that is more feasible
--- of course you can change zzz to any value that works for you and it keeps the fields from being blank I know this post is over a month old but thought it may help someone else

SELECT   
isnull(nullif(C.containerCode,''),'zzz') + ':' + isnull(nullif(C.containerName,''),'zzz') as display,
C.containerCode AS code
FROM Containers C
INNER JOIN ContainersTypes CT ON
   CT.containerTypeSqlId = C.containerTypeSqlId AND
   CT.containerTypeIncId = C.containerTypeIncId
WHERE   C.isDeleted = 0
ORDER BY display
VSSGeorge
VSSGeorge
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 1406
Thanks for the post. Nullif is new to me.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search