Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Select statement Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 4:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:40 AM
Points: 593, Visits: 1,082
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?
Post #1519162
Posted Tuesday, December 3, 2013 5:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 1,541, Visits: 8,206
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.


BrainDonor
Linkedin
Blog Site
Post #1519168
Posted Tuesday, December 3, 2013 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:47 AM
Points: 7,005, Visits: 8,451
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1519177
Posted Thursday, December 5, 2013 2:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:40 AM
Points: 593, Visits: 1,082
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

Post #1519922
Posted Tuesday, January 21, 2014 7:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 3, Visits: 162

---- 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
Post #1533442
Posted Thursday, January 23, 2014 3:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:40 AM
Points: 593, Visits: 1,082
Thanks for the post. Nullif is new to me.
Post #1533965
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse