CODEWITHSUNDEEP
sqlsql-servert-sqlcase
user380432
user380432

Reputation: 4779

SQL Case statement specifiying condition in where clause?

I have the the following query:

    SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND
@ActiveInactive =
CASE 
WHEN 'Active' THEN (o.[orderactivedate] > o.[orderinactivedate])
WHEN 'Inactive' THEN (o.[orderactivedate] < o.[orderinactivedate]) 
END

This returns 

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

How would I get this to work? saying if the parameter is 'Active' then return records with the following criteria?

Upvotes: 3

Views: 3040

Answers (4)

user745452
user745452

Reputation: 21

SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND
@ActiveInactive =
CASE 
WHEN (o.[orderactivedate] > o.[orderinactivedate]) then 'Active'
WHEN (o.[orderactivedate] < o.[orderinactivedate]) THEN 'Inactive'
END

Upvotes: 2

gbn
gbn

Reputation: 432712

You can't have an comparison expression in a CASE like that

I'd consider this option which change the comparison to a normal expression

...
AND
CASE @ActiveInactive 
WHEN 'Active' THEN DATEDIFF(day, o.[orderinactivedate], o.[orderactivedate])
WHEN 'Inactive' THEN DATEDIFF(day, o.[orderactivedate], o.[orderinactivedate])
END > 0

Or this, and you can have a computed column on the SIGN() expression

SIGN(DATEDIFF(day, o.[orderinactivedate], o.[orderactivedate])) =
              CASE WHEN @ActiveInactive WHEN 'Active' THEN 1 WHEN 'InActive' THEN -1 END

Upvotes: 1

Tasio
Tasio

Reputation: 351

Why don't you add an OR condition? like

SELECT... WHERE ...
AND ((@ActiveInactive = 'Active' AND o.[orderactivedate] > o.[orderinactivedate])  OR
    (@ActiveInactive = 'Inactive' AND o.[orderactivedate] < o.[orderinactivedate]))

Upvotes: 1

Dustin Laine
Dustin Laine

Reputation: 38553

You could do it an alternate way:

SELECT * 
FROM dbo.tblOrders o
WHERE o.OrderId IN (SELECT [Value] FROM [dbo].[udf_GenerateVarcharTableFromStringList](@OrderId, ','))
AND ((@ActiveInactive = 'Active' AND o.[orderactivedate] > o.[orderinactivedate])
OR   (@ActiveInactive = 'Inactive' AND o.[orderactivedate] < o.[orderinactivedate]))

Upvotes: 7

Related Questions