Sub Select in CASE statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sub Select in CASE statement

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-07-09 : 14:58:24

Ok, here is my issue...

In my column below TESTER, I get a NULL value for one of the values. That is what I expect. But, when I use the same SQL in my CASE statement, it ALWAYS evaluates to the ELSE, and never to the WHEN NULL. What exactly do I need to do in order to do this?



DECLARE @reportDate datetime
DECLARE @startDateMonthly datetime
DECLARE @protocolID int

SET @reportDate = '2/1/2007'
SET @startDateMonthly = '5/1/2007'
SET @protocolID = 152

DECLARE @EnrollmentGoal int
DECLARE @NoUSSites int
DECLARE @NoSites int

SELECT @EnrollmentGoal = intEnrollmentGoal, @NoUSSites = intNoUSSites, @NoSites = intNoSites
FROM tblProtocol WHERE intProtocolID = @protocolID 

SELECT
Year(am.theDate)
,DATENAME(month,am.theDate) as TheMonth
,am.theDate

,(SELECT intTransactionID FROM tblEnrollment e2 
	INNER JOIN tblSiteAlias
    ON (e2.intAliasID = tblSiteAlias.intAliasID
    and tblSiteAlias.intProtocolID = @protocolID)
WHERE YEAR(am.theDate) = YEAR(e2.dtmReportedDate)
          AND MONTH(am.theDate) = MONTH(e2.dtmReportedDate)) AS TESTER

,CASE(
SELECT intTransactionID FROM tblEnrollment e2 
	INNER JOIN tblSiteAlias
    ON (e2.intAliasID = tblSiteAlias.intAliasID
    and tblSiteAlias.intProtocolID = @protocolID)
WHERE YEAR(am.theDate) = YEAR(e2.dtmReportedDate)
          AND MONTH(am.theDate) = MONTH(e2.dtmReportedDate))
      WHEN NULL THEN 

(
SELECT TOP (1) tblEnrollment.intEnrollment FROM tblEnrollment 
	INNER JOIN tblSiteAlias ON tblEnrollment.intAliasID = tblSiteAlias.intAliasID
		WHERE (tblSiteAlias.intProtocolID = @protocolID) 
		AND (tblEnrollment.dtmReportedDate < am.theDate)
		AND (tblEnrollment.intEnrollment > 0)
ORDER BY tblEnrollment.dtmReportedDate DESC
)

      ELSE 
(
SELECT TOP 1 IsNull(SUM(intEnrollment),0) FROM tblEnrollment e2 
	INNER JOIN tblSiteAlias
    ON (e2.intAliasID = tblSiteAlias.intAliasID
    and tblSiteAlias.intProtocolID = @protocolID)
WHERE YEAR(am.theDate) = YEAR(e2.dtmReportedDate)
          AND MONTH(am.theDate) = MONTH(e2.dtmReportedDate)
)
END as TotalEnrollment

, IsNull(((CONVERT(REAL, @NoUSSites)/@NoSites) * @EnrollmentGoal),0) as USGoal
, @EnrollmentGoal as [Enrollment Goal]
FROM ALLMONTHS AS am LEFT JOIN tblEnrollment e
     ON (     YEAR(am.theDate) = YEAR(e.dtmReportedDate)
          AND MONTH(am.theDate) = MONTH(e.dtmReportedDate)
        )
WHERE am.theDate > @reportDate and am.theDate <= @startDateMonthly
GROUP BY Year(am.theDate),Month(am.theDate),DATENAME(month,am.theDate), am.theDate
ORDER BY Year(am.theDate) ASC,
Month(am.theDate) ASC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-09 : 15:24:35

Unfortunately, the CASE statement uses a equality check so Null does not equal Null. You might get around this by changeing the ANSI NULL setting. OR you can check for NULL using IS NULL. For example:

SELECT 
	CASE 
		WHEN (SELECT Val FROM MyTable) IS NULL 
			THEN (SELECT Val FROM MyOtherTable)
		ELSE (SELECT Val FROM MyOtherOtherTable)
	END AS TotalEnrollment

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources