How to get rid of NULL from a query

  • Edvard Korsbæk

    SSCertifiable

    Points: 5808

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

    Sorry - Wrong forum.

    Should have been SQL 2005 general

  • Gail Shaw

    SSC Guru

    Points: 1004454

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply