Case Statement in Where Clause

[mjk9564]

Can anyone tell me the correct syntax to do this or even if it will work. I'm using SQL Server 2005. It compiles but when it runs I get this error:
"Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

ReferralTypeID IN (
		CASE
			WHEN @OnlyNCFCH = 'True' THEN 4			--NC-FCH
			ELSE (SELECT ReferralTypeID FROM zReferralType WHERE ReferralTypeID <> 4)								
		END)

I got around it by doing the following, but eventually there could be more than two other options.

(ReferralTypeID =
		CASE
			WHEN @OnlyNCFCH = 'True' THEN 4			--NC-FCH
			ELSE 1									--FC
		END OR ReferralTypeID =
		CASE
			WHEN @OnlyNCFCH = 'True' THEN 4			--NC-FCH
			ELSE 6									--FO
		END)

 

[AlexCuse]

I think that you should make a lookup table for this.

OnlyNCFCH VAL
True 4
False 1
False 6

Then do something like this:

ReferralTypeID in (
     select VAL from myLookupTableName
     where OnlyNCFCH = @OnlyNCFCH
)

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

not tested, but try this...

Where (@OnlyNCFCH = 'True' And ReferralTypeId = 4)
      Or (ReferralTypeID IN (SELECT ReferralTypeID 
                             FROM   zReferralType 
                             WHERE  ReferralTypeID <> 4) )

-George

"the screen with the little boxes in the window." - Moron

 

[mjk9564]

gmmastros I like that better than what I have.

Do you know if there is a way to return > 1 record from a case statement w/o getting an error. Your solution will for this but I'm sure I'll run into this again.

Thanks!
Matt

 

[gmmastros]

Case statements only work with scalar values. You cannot return more that 1 record.

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

WHERE ReferralTypeID IN (
   SELECT ReferralTypeID
   FROM zReferralType
   WHERE
      ReferralTypeID <> CASE @OnlyNCFH WHEN 'True' THEN 4 ELSE 0 END -- or pick another value that will never be in the table
)

And an inner join is 90% likely to perform better:

   INNER JOIN (
      SELECT ReferralTypeID
      FROM zReferralType
      WHERE
         ReferralTypeID <> CASE @OnlyNCFH WHEN 'True' THEN 4 ELSE 0 END
   ) T ON OtherTable.ReferralTypeID = T.ReferralTypeID

Since zReferralType is a likely a very small table, there's no significant performance hit with the where clause I'm suggesting, and there is a huge boost in a lower-complexity WHERE clause or JOIN condition.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]