May 29, 2011 at 3:27 am
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
May 29, 2011 at 3:45 am
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.
May 29, 2011 at 4:00 am
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
May 29, 2011 at 4:06 am
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...
May 29, 2011 at 5:24 am
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
May 29, 2011 at 10:06 am
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