USE IN Operater in WHERE Clause with CASE.

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

General SQL Server Forums

New to SQL Server Programming

USE IN Operater in WHERE Clause with CASE.

Author

Topic

afzaal820
Starting Member

19 Posts

Posted - 2011-08-25 : 03:10:32

Declare @Tier nvarchar(max) = 'T1,T2'

Declare @IncludeNull nvarchar(max)= 1

Select RowID=ROW_NUMBER() OVER
(
ORDER BY ( PatientID )
)
FROM TraumaDetails
WHERE
TraumaDetails.[Tier] IN
(
CASE WHEN @IncludeNull = 1 THEN
(SELECT convert(NVARCHAR(max), Value) from Split(@Tier, ',') union Select NULL)
ELSE
(SELECT convert(NVARCHAR(max), Value) from Split(@Tier, ','))
END
)

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-25 : 03:11:45

So which row do you want to return in the subquery? Yours is returning more than one, and it's not allowed. So you need to change it so that it only returns 1 or rewrite your solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afzaal820
Starting Member

19 Posts

Posted - 2011-08-25 : 03:23:51

Declare @Tier nvarchar(max) = 'T1,T2'
Declare @IncludeNull nvarchar(max)= 1

Select RowID=ROW_NUMBER() OVER
(
ORDER BY ( PatientID )
)
FROM TraumaDetails
WHERE
TraumaDetails.[Tier] IN
(SELECT convert(NVARCHAR(max), Value) from Split(@Tier, ',') union Select NULL)

This is working fine why introducing the CASE its stops working.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 03:40:22

Just a guess but:


WHERE 
CASE WHEN @IncludeNull = 1 AND
TraumaDetails.[Tier] IN 
(SELECT convert(NVARCHAR(max), Value) from Split(@Tier, ',') union Select NULL)
WHEN  @IncludeNull = 0 AND
TraumaDetails.[Tier] IN 
(SELECT convert(NVARCHAR(max), Value) from Split(@Tier, ',')) 
ELSE NULL -- No valid scenario
END

but I'm curious about your code.

XXX IN ('Value1', 'Value2', NULL)

is never going to match the NULL - so adding a "union Select NULL" to your IN list achieves nothing?

(unless you have some non-standard ANSII_NULLS setting perhaps?)

afzaal820
Starting Member

19 Posts

Posted - 2011-08-25 : 05:38:47

I want to include null if the flag is true.

Actually this is part of one big query, here is just a small part. Lets take this is example

T1
T2
NULL
NULL
T1
T2
NULL

How i include NULL in the result based on flag.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 05:44:06

may be below?


....
WHERE 
(TraumaDetails.[Tier] IN 
(SELECT convert(NVARCHAR(max), Value) from Split(@Tier, ',') )
OR TraumaDetails.[Tier] IS NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

afzaal820
Starting Member

19 Posts

Posted - 2011-08-25 : 05:54:29

But I dont want to include nulls always it is based on flag from application.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 05:56:53

what flag in application will determine if NULLa are to be included?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 06:03:02

Visakh's code but modified to be:


OR (@IncludeNull = 1 AND TraumaDetails.[Tier] IS NULL))

perhaps?

afzaal820
Starting Member

19 Posts

Posted - 2011-08-25 : 06:05:31

This is based on requirements if the user want to include null in the result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 06:27:54

then do as Kirsten suggested. pass the flag value as per user input

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources