December 12, 2017 at 11:10 am
Hi,
I have a requirement where I need a SELECTIVE condition under WHERE clause which has to be applied to "certain records" only, not all.
I don't want to UNION the two sets of results, which could be one the possibilities.
Lets take a sample:
Create Table Test123
(
Column_1 int,
column_2 Char(1),
column_3 Char(1)
)
insert into Test123
values
(4, 'Y', 'A')
,(2, 'N', 'B')
,(4, 'N', 'c')
,(2, 'Y', 'D')
select * from Test123
WHERE
column_2 = 'Y'
AND
CASE
WHEN Column_1 = 4 THEN "column_3 = 'X' " -- SELECTIVE additional condition
ELSE 1=1
END
The above is not working. Any tuning or alternate way of doing it you could kindly suggest? - thanks
December 12, 2017 at 11:12 am
etl2016 - Tuesday, December 12, 2017 11:10 AMThe above is not working. Any tuning or alternate way of doing it you could kindly suggest? - thanks
Could you define "not working" please? I have a good suspicion, but it would be good for you to confirm.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 11:52 am
etl2016 - Tuesday, December 12, 2017 11:10 AMHi,
I have a requirement where I need a SELECTIVE condition under WHERE clause which has to be applied to "certain records" only, not all.I don't want to UNION the two sets of results, which could be one the possibilities.
Lets take a sample:
Create Table Test123
(
Column_1 int,
column_2 Char(1),
column_3 Char(1)
)
insert into Test123
values
(4, 'Y', 'A')
,(2, 'N', 'B')
,(4, 'N', 'c')
,(2, 'Y', 'D')
select * from Test123
WHERE
column_2 = 'Y'
AND
CASE
WHEN Column_1 = 4 THEN "column_3 = 'X' " -- SELECTIVE additional condition
ELSE 1=1
END
The above is not working. Any tuning or alternate way of doing it you could kindly suggest? - thanks
A CASE expression cannot return a Boolean value "Column_3 = 'X'". You can rewrite it as follows
AND CASE WHEN Column_1 <> 4 THEN 1 WHEN column_3 = 'X' THEN 1 ELSE 0 END = 1
Note that I've rearranged the conditions so that each WHEN clause is simple.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 12, 2017 at 3:01 pm
Specifically what row selection conditions do you want? Your sample is not clear. But you almost certainly don't need CASE in any event.
It sounds like you may actually need an "OR" between the main conditions, but it's too hard to tell to be sure.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 12, 2017 at 11:18 pm
etl2016 - Tuesday, December 12, 2017 11:10 AMHi,
I have a requirement where I need a SELECTIVE condition under WHERE clause which has to be applied to "certain records" only, not all.I don't want to UNION the two sets of results, which could be one the possibilities.
Lets take a sample:
Create Table Test123
(
Column_1 int,
column_2 Char(1),
column_3 Char(1)
)
insert into Test123
values
(4, 'Y', 'A')
,(2, 'N', 'B')
,(4, 'N', 'c')
,(2, 'Y', 'D')
select * from Test123
WHERE
column_2 = 'Y'
AND
CASE
WHEN Column_1 = 4 THEN "column_3 = 'X' " -- SELECTIVE additional condition
ELSE 1=1
END
The above is not working. Any tuning or alternate way of doing it you could kindly suggest? - thanks
Hi ,
You requirement is not very clear. Can you kindly answer the question posted by Scott and Thorn A.
Also confirm is this what you are looking for?
SELECT
Column_1,
column_2,
CASE
WHEN column_3 = 'a' THEN 'X'
END AS column_3
FROM (SELECT
Column_1,
column_2,
CASE
WHEN Column_1 = 4 AND
column_2 = 'Y' THEN column_3
END AS column_3
FROM Test123) a
WHERE column_3 IS NOT NULL
UNION
SELECT
Column_1,
column_2,
CAST(COALESCE(column_3, 1) AS varchar)
FROM (SELECT
Column_1,
column_2,
CASE
WHEN Column_1 = 4 AND
column_2 = 'Y' THEN column_3
END AS column_3
FROM Test123) a
WHERE column_3 IS NULL
ORDER BY column_3 DESC
Saravanan
Saravanan
December 13, 2017 at 2:19 am
I think you just need this where clause:
where column_2 = 'Y'
and (Column_1 <> 4 or column_3 = 'X')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply