May 29, 2011 at 3:24 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:25 am
Sorry - Wrong forum.
Should have been SQL 2005 general
May 29, 2011 at 3:41 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply