February 6, 2013 at 11:34 am
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
February 6, 2013 at 11:52 am
jbalbo (2/6/2013)
HiI 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
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.
February 6, 2013 at 2:25 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2013 at 7:37 am
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
February 7, 2013 at 8:54 am
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
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
February 7, 2013 at 9:30 am
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 😀
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
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply