February 23, 2018 at 10:21 am
I have a rather complicated query that has been working as expected but we were thrown a scenario where two variables could match to a column. Rather than include all the code I created a simple example I could play with and will include it.
declare @table table
(
column1 varchar(7),
column2 varchar(7),
column3 money
)
insert @table (column1, column2, column3)
values ('CODE','A',10.00), ('CODE','B',20.00)
declare @match1 varchar(7),
@match2 varchar(7),
@code varchar(7)
set @match1='A'
set @match2= 'B'
set @code = 'CODE'
select * from @table where column1 = @code and (column2 = @match1 or column2 = @match2)
My question is how could I write the where clause so that if column2 = @match1 it would not attempt to try @match2? I've tried mucking around with case statements with no success. I apologize for my novice level ability with this as well.
February 23, 2018 at 10:49 am
sp_harris_03 - Friday, February 23, 2018 10:21 AMI have a rather complicated query that has been working as expected but we were thrown a scenario where two variables could match to a column. Rather than include all the code I created a simple example I could play with and will include it.
declare @table table
(
column1 varchar(7),
column2 varchar(7),
column3 money
)insert @table (column1, column2, column3)
values ('CODE','A',10.00), ('CODE','B',20.00)declare @match1 varchar(7),
@match2 varchar(7),
@code varchar(7)set @match1='A'
set @match2= 'B'
set @code = 'CODE'select * from @table where column1 = @code and (column2 = @match1 or column2 = @match2)
My question is how could I write the where clause so that if column2 = @match1 it would not attempt to try @match2? I've tried mucking around with case statements with no success. I apologize for my novice level ability with this as well.
You cannot force the optimiser to do this, as far as I know. Why is it a problem?
February 23, 2018 at 11:20 am
In the past it wasn't really an issue. But I have 3 columns that behave like the above because they allow the user to input a literal value but also have that cross reference to another value. When it was just the one column I could have an If statement above my query and get the one matching row and then have a subsequent query run if there was no match. But with 3 columns behaving like this and the addition of a possible 4th I would end up with 9 or 16 possible combinations.
So in my example above A is the better match from the user's perspective because it is the literal value. That might be unclear the way I describe it. I could probably give a better example. And, I agree the optimizer cannot do what I want the way it is written.
February 23, 2018 at 11:27 am
sp_harris_03 - Friday, February 23, 2018 11:20 AMIn the past it wasn't really an issue. But I have 3 columns that behave like the above because they allow the user to input a literal value but also have that cross reference to another value. When it was just the one column I could have an If statement above my query and get the one matching row and then have a subsequent query run if there was no match. But with 3 columns behaving like this and the addition of a possible 4th I would end up with 9 or 16 possible combinations.So in my example above A is the better match from the user's perspective because it is the literal value. That might be unclear the way I describe it. I could probably give a better example. And, I agree the optimizer cannot do what I want the way it is written.
OK, that is a bit difficult to visualise. If you would take the time to write out the desired logic in a more detailed fashion, perhaps someone here will be able to help you find an alternative way of implementing it.
February 23, 2018 at 11:29 am
My example may be a bit too sparse and unclear. I will see if I can attempt to describe it better. Thank you for responding.
February 23, 2018 at 11:31 am
The reason is that you need a different approach. Each row is evaluated individually, so you need to assign something to identify them as the best option. Something like this:
WITH CTE AS(
SELECT column1,
column2,
column3,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY CASE column2 WHEN @match1 THEN 1 WHEN @match2 THEN 2 ELSE 3 END) AS rn
FROM @table
WHERE column1 = @code
AND
(
column2 = @match1
OR column2 = @match2
)
)
SELECT column1,
column2,
column3
FROM cte
WHERE rn = 1;
February 23, 2018 at 11:44 am
Luis has me on the right path... thanks!
February 23, 2018 at 12:09 pm
Luis Cazares - Friday, February 23, 2018 11:31 AMThe reason is that you need a different approach. Each row is evaluated individually, so you need to assign something to identify them as the best option. Something like this:
WITH CTE AS(
SELECT column1,
column2,
column3,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY CASE column2 WHEN @match1 THEN 1 WHEN @match2 THEN 2 ELSE 3 END) AS rn
FROM @table
WHERE column1 = @code
AND
(
column2 = @match1
OR column2 = @match2
)
)
SELECT column1,
column2,
column3
FROM cte
WHERE rn = 1;
Nice work, Luis!
February 23, 2018 at 4:36 pm
Phil Parkin - Friday, February 23, 2018 12:09 PMLuis Cazares - Friday, February 23, 2018 11:31 AMThe reason is that you need a different approach. Each row is evaluated individually, so you need to assign something to identify them as the best option. Something like this:
WITH CTE AS(
SELECT column1,
column2,
column3,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY CASE column2 WHEN @match1 THEN 1 WHEN @match2 THEN 2 ELSE 3 END) AS rn
FROM @table
WHERE column1 = @code
AND
(
column2 = @match1
OR column2 = @match2
)
)
SELECT column1,
column2,
column3
FROM cte
WHERE rn = 1;Nice work, Luis!
by the way - this type of markup also works:
column2 in ( @match1, @match2)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply