CASE STATEMENT IN WHERE CLAUSE
-
Can someone please help with a case statemnt that i am trying to use in a where clause. All i want is if if my @custom_value = 'D' i need to use the check for amount between 2 values passed as parameter and if its anything other than D I check for quantity between any 2 values again passed as parameters. Any help on thsi will be greatly appreciated.
TIA
WHERE name like '%bnb%'
and id = 100
AND CASE
WHEN @custom_value='D' then
(AL1.amount between @threshold_value1 and @threshold_value2)
ELSE
(AL1.quantity between @threshold_value1 and @threshold_value2)
END
-
Can we have the actual code... the case does nothing in this exemple!!!
I think I need a break now
. Sample code was clear the first time. -
This might be what you need but I can't test to be sure.
and id = @id
and 1 = CASE WHEN Value = 'D' AND Value2 Between @Var1 and @Var2 Then 1
when Value2 BETWEEN @Var21 AND @Var22 THEN 1
ELSE 0
END
-
SELECT col1, col2, col3, col4, col5, AL1.amount, AL1.quantity
from tab , tab2 AL1
WHERE tab.id = AL1.id
and name like '%bnb%'
and id = 100
AND CASE
WHEN @custom_value='D' then
(AL1.amount between @threshold_value1 and @threshold_value2)
ELSE
(AL1.quantity between @threshold_value1 and @threshold_value2)
END
I am passing @custom_value, @threshold_value1, @threshold_value2 as 3 input parameters
Thanks
-
AND 1 = CASE
WHEN @custom_value='D' AND
AL1.amount between @threshold_value1 and @threshold_value2 THEN 1
WHEN AL1.quantity between Threshold_value1 and @threshold_value2 THEN 1
ELSE 0
END
-
Thanks RGR'us
Just wanna make sure if @custom_value is not 'D' but some other value i need to check for AL1.quantity between Threshold_value1 and @threshold_value2 . From what you wrote if it isnt 'D' the case statement ends. In short all i need is if my input paarmetr is 'D' i am checking for an amt between 2 ranges and if its not 'D' i am looking for a qty range. Hope i made myself clear.
TIA
-
if the alue is 'd' then it checks against the first column, other than 'd' on the 2nd column. All else will return 0 and not return the row!
-
AND (
(@custom_value='D' AND AL1.amount between @threshold_value1 and @threshold_value2)
OR (@custom_value<>'D' AND AL1.quantity between @threshold_value1 and @threshold_value2)
)
I would kill for such design and such queries.
_____________
Code for TallyGenerator -
Any advantages of using your version VS mine (performance wise)?
-
Thanks for all your help.
-
You statement enforces table scan for sure.
My option leaves a chance of using indexes. If there are some. What I doubt.
_____________
Code for TallyGenerator -
Ya a chance at 2 index seeks and a concat... Thanx for the info.
-
And even your version can be smplified:
AND
CASE WHEN @custom_value='D' then AL1.amount ELSE AL1.quantity END
between @threshold_value1 and @threshold_value2
Not sure this will use any index (there is a small chance), but at least it's easier to read.
But, you know, Amount is what? Money? Quantity is float, decimal? Both are compared to the same variables...
Not to mention such words and entities, properties... I'm afraid they become offensive words.
_____________
Code for TallyGenerator -
How about:
WHERE name like '%bnb%'
and id = 100
AND ( CASE @custom_value
WHEN 'D' THEN AL1.amount
ELSE AL1.quantity
END) between @threshold_value1 and @threshold_value2
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
-
Apologies, Sergiy - I hadn't yet noticed that there was a second page when I posted. Ours are functionally identical.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply