SQL Select statement

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  December 3, 2013 at 4:37 am

  #287084

  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

  WHEREC.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

  SSCoach

  Points: 19252

  More actions

  December 3, 2013 at 5:01 am

  #1670340

  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

• Johan Bijnens

  SSC Guru

  Points: 135253

  More actions

  December 3, 2013 at 5:32 am

  #1670348

  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
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

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

  - 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

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  December 5, 2013 at 2:40 am

  #1670963

  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

  WHEREC.isDeleted = 0

  ORDER BY display

• domainsource

  SSC Rookie

  Points: 33

  More actions

  January 21, 2014 at 7:59 pm

  #1682247

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

  WHEREC.isDeleted = 0

  ORDER BY display

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  January 23, 2014 at 3:11 am

  #1682682

  Thanks for the post. Nullif is new to me.