How to get rid of NULL from a query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Compact Edition » How to get rid of NULL from a query

How to get rid of NULL from a query

Rate Topic

Display Mode

Topic Options

Author

Message

edvard 19773

Posted Sunday, May 29, 2011 3:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 42, Visits: 83

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

edvard 19773

Posted Sunday, May 29, 2011 3:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 42, Visits: 83

Sorry - Wrong forum.
Should have been SQL 2005 general

Post #1116745

GilaMonster

Posted Sunday, May 29, 2011 3:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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 »

Permissions