Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CASE IN in where clause Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:07 AM
Points: 2, Visits: 8
I am trying to select different data from a table depending on what the passed parameter value is. If the parameter is 0 then select all products from plu_file_dates with ToDept value of 60 in the plu_file or else select all products with ToDept of 60 and FromDept with value matching parameter.
However no matter how I try to write the where statement it always fails to execute

DECLARE @Dept AS INT
SET @Dept = 40

SELECT * FROM plu_file_dates
WHERE pluid in (SELECT pluid FROM plu_file WHERE (CASE WHEN @Dept = 0 THEN (ToDept = 60) ELSE (FromDept = @Dept and ToDept = 60) END))
Post #1421541
Posted Tuesday, February 19, 2013 4:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 2,422, Visits: 7,442
You can fix the syntax like this: -
DECLARE @Dept AS INT
SET @Dept = 40

SELECT *
FROM plu_file_dates
WHERE pluid IN (SELECT pluid
FROM plu_file
WHERE ToDept = 60 AND FromDept = CASE WHEN @Dept = 0
THEN FromDept
ELSE @Dept END
);

However, this isn't the best way for the query optimiser to work, see Gail's blog post here --> http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Instead, do something like this: -
DECLARE @Dept AS INT;
SET @Dept = 40;

IF @Dept = 0
BEGIN
SELECT *
FROM plu_file_dates
WHERE pluid IN (SELECT pluid
FROM plu_file
WHERE ToDept = 60
);
END
ELSE BEGIN
SELECT *
FROM plu_file_dates
WHERE pluid IN (SELECT pluid
FROM plu_file
WHERE ToDept = 60 AND FromDept = @Dept
);
END




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1421550
Posted Tuesday, February 19, 2013 4:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 251, Visits: 581
it think the way you have written the query is not right
case statement should be the part of the expression
in your case you can use case like this
select * from sys.objects
where name=(case when name='t1 ' then 'condition '
else 'condition2'
end)
Post #1421553
Posted Tuesday, February 19, 2013 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:07 AM
Points: 2, Visits: 8
Thanks, used the second solution and works perfectly
Post #1421556
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse