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 from a query Expand / Collapse
Author
Message
Posted Sunday, May 29, 2011 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 100, Visits: 174
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 #1116743
Posted Sunday, May 29, 2011 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 100, Visits: 174
Sorry - Wrong forum.
Should have been SQL 2005 general
Post #1116745
Posted Sunday, May 29, 2011 3:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 40,194, Visits: 36,599
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1116748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse