|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Case statement 'Operator' in Where clausei have a sp like this
--------------------------------- @operator int --(1=smaller,2=bigger) @price decimal(18,2) select id,price from company where (price case @operator when 1 then <@price when 2 then >@price end ) --------- how can i accomplish this? thnx declare @operator int,
@price decimal(18,2) select ID, Price from Company where (price < @price and @operator = 1 ) and (price > @price and @operator = 2) But if you have this in a Store procedure declare @operator int, @price decimal(18,2) if @operator = 1 begin select ID, Price from Company where price < @price end else if @operator = 2 begin select ID, Price from Company where price > @price end Also with the logic that you have if the price is the same you will never see it. So you need to do this if was in procedure declare @operator int, @price decimal(18,2) if @operator = 1 begin select ID, Price from Company where price <= @price end else if @operator = 2 begin select ID, Price from Company where price >= @price end thanks for your reply
i realy would like to do this in one statment without making use of if-else. the reason behind this is that i have multiple operators for multiple columns and it realy gets complex with 4 or 5 operators. is it possible or do i have to use the if-else senario? Show quote "Amiller" wrote: > declare @operator int, > @price decimal(18,2) > > > select ID, Price > from Company > where (price < @price and @operator = 1 ) > and (price > @price and @operator = 2) > > But if you have this in a Store procedure > > declare @operator int, > @price decimal(18,2) > > > if @operator = 1 > begin > select ID, Price > from Company > where price < @price > end > else if @operator = 2 > begin > select ID, Price > from Company > where price > @price > end > > > > Also with the logic that you have if the price is the same you will > never see it. > So you need to do this if was in procedure > declare @operator int, > @price decimal(18,2) > > > if @operator = 1 > begin > select ID, Price > from Company > where price <= @price > end > else if @operator = 2 > begin > select ID, Price > from Company > where price >= @price > end > > I am not sure if the first statement will work i have never tried
something like that. I would go with the if else and comment all layers so you know what one does what. On Wed, 25 Jan 2006 13:38:01 -0800, henk wrote:
>i have a sp like this Hi Henk,>--------------------------------- >@operator int --(1=smaller,2=bigger) >@price decimal(18,2) > >select id,price >from company >where (price case @operator when 1 then <@price when 2 then >@price end ) > >--------- >how can i accomplish this? >thnx IF @operator = 1 SELECT id, price FROM company WHERE price < @price ELSE SELECT id, price FROM company WHERE price > @price -- Hugo Kornelis, SQL Server MVP You can use either options as follows
declare @operator int --(1=smaller,2=bigger) declare @price decimal(18,2) set @operator = 1 -- 2 set @price = 10.05 -- Option A declare @cmd varchar(8000) set @cmd = 'select id, price from company where price '+ case @operator when 1 then '< @price' else '> @price' end exec (@cmd) -- Option B if @operator = 1 select id ,price from company where price < @price if @operator = 2 select id ,price from company where price >@price Show quote "henk" wrote: > i have a sp like this > --------------------------------- > @operator int --(1=smaller,2=bigger) > @price decimal(18,2) > > select id,price > from company > where (price case @operator when 1 then <@price when 2 then >@price end ) > > --------- > how can i accomplish this? > thnx > DECLARE @operator int
DECLARE @price decimal (18,2) SET @price = 17.99 --To find items that are less than @price, set @operator to 1 --To find items that are more than @price, set @operator to 2 SELECT id, price FROM company WHERE CASE WHEN price - @price > 0 THEN 2 WHEN price - @price <= 0 THEN 1 END = @operator The following was tested on Northwind USE Northwind GO DECLARE @operator int DECLARE @price money SET @operator = 1 SET @price = 17.95 select productid, unitprice FROM Products WHERE CASE WHEN UnitPrice > @price THEN 2 WHEN UnitPrice < @price THEN 1 END = @operator -- Show quote"henk" wrote: > i have a sp like this > --------------------------------- > @operator int --(1=smaller,2=bigger) > @price decimal(18,2) > > select id,price > from company > where (price case @operator when 1 then <@price when 2 then >@price end ) > > --------- > how can i accomplish this? > thnx > thank you all for your helpfull replys.
i think i go with the Mark 's solution. that is exactly what i wanted. thank you once again. Show quote "Mark Williams" wrote: > DECLARE @operator int > DECLARE @price decimal (18,2) > > SET @price = 17.99 > > --To find items that are less than @price, set @operator to 1 > --To find items that are more than @price, set @operator to 2 > > SELECT id, price > FROM company > WHERE > CASE > WHEN price - @price > 0 THEN 2 > WHEN price - @price <= 0 THEN 1 > END > = @operator > > > > The following was tested on Northwind > > USE Northwind > GO > > DECLARE @operator int > DECLARE @price money > > SET @operator = 1 > SET @price = 17.95 > > select productid, unitprice > FROM Products > WHERE > CASE > WHEN UnitPrice > @price THEN 2 > WHEN UnitPrice < @price THEN 1 > END = @operator > > -- > > > "henk" wrote: > > > i have a sp like this > > --------------------------------- > > @operator int --(1=smaller,2=bigger) > > @price decimal(18,2) > > > > select id,price > > from company > > where (price case @operator when 1 then <@price when 2 then >@price end ) > > > > --------- > > how can i accomplish this? > > thnx > > Just to clean things up
DECLARE @operator int DECLARE @price decimal (18,2) SET @price = 17.99 --To find items that are less than @price, set @operator to 1 --To find items that are more than @price, set @operator to 2 SELECT id, price FROM company WHERE CASE WHEN price > @price THEN 2 WHEN price <= @price THEN 1 END = @operator Not sure why I did the whole price - @price > 0 thing when the above is much cleaner. -- Show quote"henk" wrote: > thank you all for your helpfull replys. > i think i go with the Mark 's solution. that is exactly what i wanted. > thank you once again. > > Please post real code so people can test it. Also why did you invent a
word like "statement operator" -- CASE is an expression in SQL. CREATE PROCEDURE ComparePrices (@high_low_flag CHAR(1) -- 'H'= higher or equal, 'L'= lower @test_price DECIMAL (18,2) ) AS SELECT company_id, company_price FROM Companies -- or is there only one company as you said in your code? WHERE CASE WHEN (@high_low_flag = 'L' AND @test_price < company_price THEN 'T' WHEN (@high_low_flag = 'H' AND @test_price >= company_price THEN 'T' ELSE 'F' = 'T' END; "id" is too vague to be data element name -- what does it identify? Likewise, "price" begs the question "price of what?" I assuemt hat you are buying companies from what you posted. In the future, post DDL and try to follow ISO-11179 standards. ISO-11179 requires names to be singular and lower case.
What on earth is ELSE 'F' = 'T' END suppose to do? Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1138230528.284439.318570@g44g2000cwa.googlegroups.com... > Please post real code so people can test it. Also why did you invent a > word like "statement operator" -- CASE is an expression in SQL. > > CREATE PROCEDURE ComparePrices > (@high_low_flag CHAR(1) -- 'H'= higher or equal, 'L'= lower > @test_price DECIMAL (18,2) ) > AS > SELECT company_id, company_price > FROM Companies -- or is there only one company as you said in your > code? > WHERE CASE WHEN (@high_low_flag = 'L' AND @test_price < company_price > THEN 'T' > WHEN (@high_low_flag = 'H' AND @test_price >= > company_price > THEN 'T' > ELSE 'F' = 'T' END; > > "id" is too vague to be data element name -- what does it identify? > Likewise, "price" begs the question "price of what?" I assuemt hat > you are buying companies from what you posted. In the future, post DDL > and try to follow ISO-11179 standards. > Tony Rogerson wrote:
> ISO-11179 requires names to be singular and lower case. Really? I've read various derivatives, can't recall the source now. Mynotes say PascalCase separated by underscore characters i.e. ObjectClass_QualifierTerm_PropertyTerm_RepresentationTerm I've seen papers that use UPPERCASE space separated. Celko certainly uses lowercase underscore separated. My personal preference is to go with Celko's because I'm averse to case sensitivity (or is it sycophantism <g>?) Jamie. --  |
|||||||||||||||||||||||
Other interesting topics