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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply