If or case staement in a where?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » If or case staement in a where?

If or case staement in a where?

Rate Topic

Display Mode

Topic Options

Author

Message

jbalbo

Posted Wednesday, February 06, 2013 11:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:00 PM
Points: 152, Visits: 222

Hi

I have this in a where :
AND
CASE WHEN @InfoKey = 5 THEN
CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)
ELSE
CLIENT_IDENTIFIER_TYPE.Code = @Code
END

I tried with an if w/ no luck

Is it the way I have the syntax?

Thanks in Advance
Joe

Post #1416661

sturner

Posted Wednesday, February 06, 2013 11:52 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 1,315, Visits: 2,887

jbalbo (2/6/2013)

That should work. or you could say:

and ((@InfoKey = 5 and CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030) OR
(@InfoKey != 5 and CLIENT_IDENTIFIER_TYPE.Code = @Code))

The probability of survival is inversely proportional to the angle of arrival.

Post #1416671

ScottPletcher

Posted Wednesday, February 06, 2013 2:25 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

AND
CASE WHEN @InfoKey = 5 THEN
CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)
ELSE
CLIENT_IDENTIFIER_TYPE.Code = @Code
END

The "THEN" and "ELSE" parts of a CASE statement must evaluate to a single value. The value can be specified by an arbitrarily complex expression, but ultimately it must be resolvable to a single value.

But the expression cannot include any column names, keywords or operators that are not part of an expression to generate a value.

So, for example, these would be OK:
THEN cola
THEN isnull(cola, '') + case when colb = 'a' then 'left' else right' end + colc + cast(cold as varchar(30))

But this would not:
ORDER BY CASE WHEN @ord = 'A' THEN ASC ELSE DESC END --ASC/DESC are keywords, not values

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1416738

opc.three

Posted Thursday, February 07, 2013 7:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 6,730, Visits: 11,782

For the record it's a CASE expression not a CASE statement.

USE tempdb;

IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT NULL 
    DROP TABLE #CLIENT_IDENTIFIER_TYPE;
GO

CREATE TABLE #CLIENT_IDENTIFIER_TYPE (Code CHAR(3));
INSERT  INTO #CLIENT_IDENTIFIER_TYPE
        (Code)
VALUES  ('028'),
        ('029'),
        ('030'),
        ('031');

DECLARE 
	@InfoKey INT = NULL,
	--@InfoKey INT = 5,
    @Code CHAR(3) = '031';

SELECT  *
FROM    #CLIENT_IDENTIFIER_TYPE AS CLIENT_IDENTIFIER_TYPE
WHERE   (
         (
          @InfoKey = 5
          AND CLIENT_IDENTIFIER_TYPE.Code IN (028, 029, 030)
         )
         OR (
             ISNULL(@InfoKey, 0) != 5
             AND CLIENT_IDENTIFIER_TYPE.Code = @Code
            )
        );

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1417072

ChrisM@Work

Posted Thursday, February 07, 2013 8:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

It can be easier to visualise complex filters by switching them into a CROSS APPLY for testing. Shamelessly nicking Orlando's setup, it might look like this;

USE tempdb;

IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT NULL 
    DROP TABLE #CLIENT_IDENTIFIER_TYPE;
GO

CREATE TABLE #CLIENT_IDENTIFIER_TYPE (Code CHAR(3));
INSERT  INTO #CLIENT_IDENTIFIER_TYPE
        (Code)
VALUES  ('028'),
        ('029'),
        ('030'),
        ('031');

DECLARE 
	@InfoKey INT = NULL,
	--@InfoKey INT = 5,
    @Code CHAR(3) = '031';

SELECT 
	*, 
	x.SimpleFilter
FROM #CLIENT_IDENTIFIER_TYPE AS CLIENT_IDENTIFIER_TYPE
CROSS APPLY (
	SELECT SimpleFilter = CASE
		WHEN @InfoKey = 5 AND CLIENT_IDENTIFIER_TYPE.Code IN (028, 029, 030) THEN 1
		WHEN (@InfoKey <> 5 OR @InfoKey IS NULL) AND CLIENT_IDENTIFIER_TYPE.Code = @Code THEN 2
		ELSE NULL END 
) x
WHERE x.SimpleFilter IS NOT NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1417134

opc.three

Posted Thursday, February 07, 2013 9:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 6,730, Visits: 11,782

ChrisM@Work (2/7/2013)

It can be easier to visualise complex filters by switching them into a CROSS APPLY for testing. Shamelessly nicking Orlando's setup, it might look like this;

USE tempdb;

IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT NULL 
    DROP TABLE #CLIENT_IDENTIFIER_TYPE;
GO

CREATE TABLE #CLIENT_IDENTIFIER_TYPE (Code CHAR(3));
INSERT  INTO #CLIENT_IDENTIFIER_TYPE
        (Code)
VALUES  ('028'),
        ('029'),
        ('030'),
        ('031');

DECLARE 
	@InfoKey INT = NULL,
	--@InfoKey INT = 5,
    @Code CHAR(3) = '031';

SELECT 
	*, 
	x.SimpleFilter
FROM #CLIENT_IDENTIFIER_TYPE AS CLIENT_IDENTIFIER_TYPE
CROSS APPLY (
	SELECT SimpleFilter = CASE
		WHEN @InfoKey = 5 AND CLIENT_IDENTIFIER_TYPE.Code IN (028, 029, 030) THEN 1
		WHEN (@InfoKey <> 5 OR @InfoKey IS NULL) AND CLIENT_IDENTIFIER_TYPE.Code = @Code THEN 2
		ELSE NULL END 
) x
WHERE x.SimpleFilter IS NOT NULL

Another great use of APPLY. If you were shameless you would have ripped it off without saying mych, like I did to sturner's example BigGrin

Just noticed I should have nicked up some single quotes on the IN too in order to avoid the implicit data type conversion to INT:

WHEN @InfoKey = 5 AND CLIENT_IDENTIFIER_TYPE.Code IN ('028', '029', '030') THEN 1

Post #1417186

« Prev Topic | Next Topic »

Permissions