Geek asks and answers

Statement of fulfilling the SQL condition in the where clause

I have 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

It returns

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

How do I make this work? saying that if the parameter is "Active", then records with the following criteria are returned?

+3
source share
4 answers

You can do this in an alternative 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]))
+7
source 
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
+2
source

Why don't you add an OR condition? as

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

You cannot have a comparison expression in CASE like

I would consider this option, which changes the comparison with 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 in the expression SIGN()

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

More articles:


All Articles