How to get rid of NULL from a query

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  May 29, 2011 at 3:24 am

  #241165

  Hi all!

  I have this query, which is a bit long, and difficult to show in this forum, but the general layout is:

  Select field,field

  (Select something) As NAME1,

  (Select something else) AS NAME 2

  WHERE some condition

  ORDER BY NAME1, NAME 2

  Problem is, that NAME2 can select nothing, leaving NAME2 as <NULL>.

  I want to have NAME2 changed to 0 if NAME2 = <NULL>

  The complete query is below, its the SUBSTAT which is the problem.

  HOW to?

  Best regards

  Edvard Korsbæk

  Original QUERY:

  SELECT A.EmployeeGroupID,A.EmployeeID,

  (SELECT RTRIM(B.PER_FORNAVN) +' '+ B.PER_EFTERNAVN FROM dbo.PERSONAL B WHERE B.PER_PERSONAL_ID=A.EmployeeID) AS FNAME,

  (SELECT C.PER_TIMER FROM dbo.PERSONAL C WHERE C.PER_PERSONAL_ID=A.EmployeeID) AS PTIME,

  (SELECT D.PER_FASTE FROM dbo.PERSONAL D WHERE D.PER_PERSONAL_ID=A.EmployeeID) AS ACTIVE,

  (SELECT E.PER_FRATRAADT FROM dbo.PERSONAL E WHERE E.PER_PERSONAL_ID=A.EmployeeID) AS RESIGNED,

  (SELECT Convert(CHAR,E.OnLeaveStart,23) FROM dbo.PERSONAL E WHERE E.PER_PERSONAL_ID=A.EmployeeID) AS LSDATE,

  (SELECT Convert(CHAR,E.OnLeaveEnd,23) FROM dbo.PERSONAL E WHERE E.PER_PERSONAL_ID=A.EmployeeID) AS LEDATE,

  (Select SubGroupType from dbo.subgroup where Subgroup_ID in (Select Subgroup_ID from dbo.employeegroupmaptoemployee where employeeID = A.EmployeeID and employeegroupid = A.EmployeeGroupID)) as Substat

  FROM dbo.EmployeeGroupMapToEmployee A, dbo.EmployeeGroup F

  WHERE A.EmployeeGroupID = F.Id AND (F.Enable = 0 OR F.Enable IS NULL OR A.EmployeeGroupID IN

  (SELECT G.EmployeeGroupID FROM dbo.dutyRosterShift G WHERE G.DutyRosterId = 3 GROUP BY G.EmployeeGroupID))

  ORDER BY A.EmployeeGroupID,substat, FNAME

• Edvard Korsbæk

  SSCertifiable

  Points: 5999

  More actions

  May 29, 2011 at 3:25 am

  #1331406

  Sorry - Wrong forum.

  Should have been SQL 2005 general

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 29, 2011 at 3:41 am

  #1331408

  No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic1116746-149-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass