Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 298
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7005 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 298
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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7005 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Edvard Korsbæk
Edvard Korsbæk
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 298
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search