Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get rid of <NULL> as result from a 'AS name' Expand / Collapse
Author
Message
Posted Sunday, May 29, 2011 3:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
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
Post #1116746
Posted Sunday, May 29, 2011 3:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 6,829, Visits: 13,290
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
Post #1116751
Posted Sunday, May 29, 2011 4:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
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
Post #1116754
Posted Sunday, May 29, 2011 4:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 6,829, Visits: 13,290
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
Post #1116755
Posted Sunday, May 29, 2011 5:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
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
Post #1116760
Posted Sunday, May 29, 2011 10:06 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1116785
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse