How to get rid of <NULL> as result from a 'AS name'

  • 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

  • T oanswer your original question: use ISNULL(NAME2,'0').

    Please note that you should use consistent data types (not an integer to replace a varchar value).

    But the much more important issue you should worry about is the general design of the query:

    Why do you use subqueries against the very same table again to select different columns instead of a single join to this table?

    I strongly recommend a complete redesign of the query.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks for a very fast reply!

    Unfortunately ISNULL(substat,0) errors out with "Invalid column name 'substat'."

    I have inherited this query, where i needed one extra column to be able to sort on 'Substat', or more specific to sort on SubGroupType.

    I think, that the reson behind the subqueries is the formatting.

    To rewrite the query is a bit ower my head - To add the 'substat' line was difficult enough.

    Best regards

    Edvard Korsbæk

  • Replace

    Select SubGroupType from dbo.subgroup

    with

    Select ISNULL(SubGroupType,0) from dbo.subgroup

    I hope it's a rather small database and performance is not important...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks again - That was my first thought, and i tried it with no succes.

    It don't work, because the problem ain't that SubGroupType is <Null>, but that it don't exist, making substat <NULL>.

    On all the other subcalls there is an answer - This one is different, as it can have no answer.

    What i should have was a line

    IF substat = <NULL> then SUBSTAT = 0 END

    Which is the purpose of ISNULL, but it needs a db field as source - I really think I am stuck here.

    OK, I can read the result of the query in a queue, and handle it from there - Its max 100 records.

    Best regards

    Edvard Korsbæk

  • I have to agree with the others - this should be re-written to eliminate all of the sub-queries. Not only will that help in performance, but the query will be much easier to read and manage.

    This really isn't that hard to do - first, I restructured the query so I could see it better. Then, I changed to query join from an implicit inner join to an explicit inner join - then added in the other tables as outer joins to get the columns you are looking for.

    Select a.EmployeeGroupID

    ,a.EmployeeID

    ,rtrim(p.PER_FORNAVN) + ' ' + p.PER_EFTERNAVN As FNAME

    ,p.PER_TIMER As PTIME

    ,p.PER_FASTE As Active

    ,p.PER_FRATRAADT As RESIGNED

    ,convert(char, p.OnLeaveStart, 23) As LSDATE

    ,convert(char, p.OnLeaveEnd, 23) As LEDATE

    ,(Select s.SubGroupType

    From dbo.Subgroup s

    Where s.Subgroup_ID In (Select e.Subgroup_ID

    From dbo.EmployeeGroupMapToEmployee e

    Where e.EmployeeID = a.EmployeeID

    And e.EmployeeGroupID = a.EmployeeGroupID))

    -- ,s.SubGroupType As Substat -- this should replace the above sub-queries when you un-comment the tables below

    From dbo.EmployeeGroupMapToEmployee a

    Inner Join dbo.EmployeeGroup f On a.EmployeeGroupID = f.Id

    Left Join dbo.Personal p On p.Per_Personal_ID = a.EmployeeID

    --**** you should be able to use the following to replace the above subqueries

    --Left Join dbo.EmployeeGroupMapToEmployee e On e.EmployeeID = a.EmployeeID

    --Left Join dbo.SubGroup s On s.SubGroup_ID = e.SubGroup_ID

    Where 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

    EmployeeGroupID

    ,Substat

    ,FNAME;

    Now that we have this, eliminating NULLS is much easier - because now we can just wrap the columns with either an ISNULL or COALESCE.

    In the above, we now have the Personal table outer joined into the query - which eliminates all of those sub-queries. I would validate whether or not there will always be a record in Personal for every Employee - if so, change the join to an Inner Join.

    A couple of other notes: the last sub-queries in the select can be replaced by adding the two tables as joins instead. Test both ways and validate the results to make sure - and, if there are issues post back and we can work those out.

    Also, when using convert - I always recommend that you specify the length of the characters. For example: convert(char, somecolumn, style) should be changed to: convert(char(nn), somecolumn, style). In your case, I wouldn't recommend using convert at all - since these appear to be datetime columns and you really should return them to the caller as datetimes. However, that would require changes on the client side at this point - which might cause problems.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 5 (of 5 total)

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