July 29, 2013 at 3:34 am
I have the following SQL statement (It's actually MYSQL but the principal is the same 😉 ) that selects all the values from a table based upon the row EditionID passed to it via $POST - in this example EditionID is 10
The query gives me 2 values as a tolerance + and - 10 on which to base the list from using BETWEEN
However, I want the @tol variable to change to a larger tolerance (lets say 20) if Value1 is >= the integer specified in the variable @trig.
I'm not sure how to go about this - I'm guessing that the Value1 values need to be retrieved via the sub queries before making another pass at the sub queries, this time with the correct @tol variable.
Unless somehow I can set 2 variables and the query chooses the correct one?
This is what I have got so far
SET @tol = 10;
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
HAVING Value1
BETWEEN
(SELECT Value1, TRUNCATE(Value1-@tol,1) as Value1min
FROM t1 WHERE EditionID = 10) iTable1)
AND
(SELECT Value1, TRUNCATE(Value1+@tol,1) as Value1max
FROM t1 WHERE EditionID = 10) iTable2)
ORDER BY Value1
Edit: Apologies errors at my end have duplicated the Post
July 29, 2013 at 4:00 am
I don't think that will even parse, since (a) you've used HAVING instead of WHERE and (b) your parentheses don't match. Also, why do you declare @trig and not use it in the code?
John
July 29, 2013 at 6:15 am
Apologies I've had to simplify the query for this post and that variable has been left in - I've removed it from the original post.
I'm using HAVING instead of WHERE as the sub query TRUNCATE is an aggregation and the WHERE doesn't handle these.
I'll have to apologise too as I'm a relative beginner to this.
I'm thinking more along the lines of using an IF or CASE statement in a Stored Procedure instead of solely in a SQL Select statement
July 29, 2013 at 6:33 am
The parentheses still don't match.
TRUNCATE isn't an aggregate function in T-SQL, as far as I know. What does it do in MySQL? If you could supply some table DDL (CREATAE TABLE statement), sample data (INSERT statements) and expected results, that may help us see what you're trying to do.
John
July 29, 2013 at 7:29 am
John Mitchell-245523 (7/29/2013)
The parentheses still don't match.TRUNCATE isn't an aggregate function in T-SQL, as far as I know. What does it do in MySQL? If you could supply some table DDL (CREATAE TABLE statement), sample data (INSERT statements) and expected results, that may help us see what you're trying to do.
John
Sorry, it is a function but not an aggregate function - MySQL TRUNCATE() is similar to the TRUNC function in TSQL - I could have probably have used ROUND(): returns a number as argument truncated up to a number (specified as another argument) of decimal places. - See more at: http://www.w3resource.com/mysql/mathematical-functions/mysql-truncate-function.php#sthash.4EevddXV.dpuf%5B/i%5D
EditionID |Make | Model | EditionDesc | Value1
1 |Ford | Mustang | 350 V8 | 300
2 |Ford | Mustang | Boss | 280
3 |Ford | Mustang | 420 | 320
4 |Ford | Mustang | Shelby GT500 | 500
5 |Chevrolet | Camaro | 350 V8 | 600
Say I've got a table of Cars and the Value1 column is the horsepower.
The query selects the cars which have a similar horsepower based upon the @tol variable.
So if the car in the sub queries is EditionID = 1 and the @tol is set to 50 then the EditionID will return all cars within a 100 horsepower range of Value1 '300' + or - 50. In this example the query above would return rows 2 and 3.
2 |Ford | Mustang | Boss | 280
3 |Ford | Mustang | 420 | 320
However, what I want to happen is when the horsepower (Value1) is for example >= 500 the query either changes or selects a value which has a greater range - so @tol = 100.
Maybe an IF or CASE statement along the lines of
IF Value1 <500 SET @tol = 50
ELSE SET @tol = 100
hope that makes sense
July 29, 2013 at 7:36 am
Why not use a percentage for the tolerance?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2013 at 7:53 am
thunderousity (7/29/2013)
Sorry, it is a function but not an aggregate function
OK, so HAVING isn't going to work. But never mind. What you're looking for is something like this:
BETWEEN @tol - CASE WHEN Value1 > 500 THEN 100 ELSE 50 END
AND @tol + CASE WHEN Value1 > 500 THEN 100 ELSE 50 END
Or, as Jason suggests, see if you can use a percentage.
John
July 30, 2013 at 4:32 am
John Mitchell-245523 (7/29/2013)
thunderousity (7/29/2013)
Sorry, it is a function but not an aggregate functionOK, so HAVING isn't going to work. But never mind. What you're looking for is something like this:
BETWEEN @tol - CASE WHEN Value1 > 500 THEN 100 ELSE 50 ENDAND @tol + CASE WHEN Value1 > 500 THEN 100 ELSE 50 END
Or, as Jason suggests, see if you can use a percentage.
John
Brilliant - that was what I needed - I nearly had it at one point but put the 'as Value1min' and 'as Value1max' at the end of the THEN and ELSE instead of just at the end of the CASE - I Might use the percentage as suggested too.
SET @tol1 = 10;
SET @tol2 = 200;
SET @TRIG = 400;
SET @car = 111;
(n.b. Value1 is an aggregation - for simplicity it is just Value1 - hence using HAVING in the BETWEEN)
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
HAVING Value1
BETWEEN
(SELECT CASE WHEN Value1 <= @trig
THEN TRUNCATE(Value1-@tol1,1)
ELSE TRUNCATE(Value1-@tol2,1)
END as Value1min
FROM t1 WHERE EditionID = @car) iTable1)
AND
(SELECT CASE WHEN Value1 <= @trig
THEN TRUNCATE(Value1+@tol1,1)
ELSE TRUNCATE(Value1+@tol2,1)
END as Value1max
FROM t1 WHERE EditionID = @car) iTable2)
ORDER BY Value1
July 30, 2013 at 4:37 am
Do you have to read the same table three times or would something like this work?
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
HAVING Value1 BETWEEN
(CASE WHEN Value1 <= @trig
THEN TRUNCATE(Value1-@tol1,1)
ELSE TRUNCATE(Value1-@tol2,1)
END)
AND
(CASE WHEN Value1 <= @trig
THEN TRUNCATE(Value1+@tol1,1)
ELSE TRUNCATE(Value1+@tol2,1)
END)
WHERE EditionID = @car
ORDER BY Value1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 5:33 am
That doesn't work as the WHERE just selects that car
July 30, 2013 at 6:10 am
thunderousity (7/30/2013)
That doesn't work as the WHERE just selects that car
Gotcha. I think an efficient TSQL version might use EXISTS, something like this:
SELECT *
FROM MyTable
WHERE EXISTS (
SELECT 1 FROM MyTable c
WHERE car = @car AND MyTable.horsepower BETWEEN c.horsepower * X AND c.horsepower * Y
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 7:03 am
ChrisM@Work (7/30/2013)
thunderousity (7/30/2013)
That doesn't work as the WHERE just selects that carGotcha. I think an efficient TSQL version might use EXISTS, something like this:
SELECT *
FROM MyTable
WHERE EXISTS (
SELECT 1 FROM MyTable c
WHERE car = @car AND MyTable.horsepower BETWEEN c.horsepower * X AND c.horsepower * Y
)
Hmmm - had to do a bit of reading to try and understand this - it's a few years ago since I did basic SQL :w00t: - I've learned loads in this post though 🙂
EXISTS looks like an efficient way of filtering - however:
If I understand this correctly will this not just give me a list of cars based upon a single variable. Which is the stage I was at at the beginning of the post. This is fine for efficiency, but the main crooks of my initial problem is to get the query to change the range used based upon the horsepower of the car.
Am I misunderstanding or perhaps I'm not seeing the potential of your EXISTS example
July 30, 2013 at 7:22 am
thunderousity (7/30/2013)
ChrisM@Work (7/30/2013)
thunderousity (7/30/2013)
That doesn't work as the WHERE just selects that carGotcha. I think an efficient TSQL version might use EXISTS, something like this:
SELECT *
FROM MyTable
WHERE EXISTS (
SELECT 1 FROM MyTable c
WHERE car = @car AND MyTable.horsepower BETWEEN c.horsepower * X AND c.horsepower * Y
)
Hmmm - had to do a bit of reading to try and understand this - it's a few years ago since I did basic SQL :w00t: - I've learned loads in this post though 🙂
EXISTS looks like an efficient way of filtering - however:
If I understand this correctly will this not just give me a list of cars based upon a single variable. Which is the stage I was at at the beginning of the post. This is fine for efficiency, but the main crooks of my initial problem is to get the query to change the range used based upon the horsepower of the car.
Am I misunderstanding or perhaps I'm not seeing the potential of your EXISTS example
The inner query is correlated to the outer query. Whatever the inner query returns, you can calculate new values from the result set to correlate to the outer query - and of course, it's the outer query which returns the results to you.
So you filter the inner query on model or whatever, calculate a rangehigh and rangelow of horsepower, then correlate those values with the outer query horsepower.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 2:52 pm
DECLARE @Tol1 AS INT
, @Tol2 AS INT
, @Trig AS INT
, @car AS INT
, @HP AS INT
SET @tol1 = 50;
SET @tol2 = 200;
SET @TRIG = 400;
SET @car = 1; --Ford Mustang 350 V8 300HP
SET @HP = (SELECT Value1 FROM t1 WHERE EditionID = @Car)
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
WHERE (Value1 BETWEEN @HP - @Tol1 AND @HP + @Tol1
AND @HP < @Trig
)
OR
(Value1 BETWEEN @HP - @Tol2 AND @HP + @Tol2
AND @HP >= @Trig
)
July 31, 2013 at 8:35 am
R. Brush (7/30/2013)
DECLARE @Tol1 AS INT, @Tol2 AS INT
, @Trig AS INT
, @car AS INT
, @HP AS INT
SET @tol1 = 50;
SET @tol2 = 200;
SET @TRIG = 400;
SET @car = 1; --Ford Mustang 350 V8 300HP
SET @HP = (SELECT Value1 FROM t1 WHERE EditionID = @Car)
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
WHERE (Value1 BETWEEN @HP - @Tol1 AND @HP + @Tol1
AND @HP < @Trig
)
OR
(Value1 BETWEEN @HP - @Tol2 AND @HP + @Tol2
AND @HP >= @Trig
)
This works a treat - a lot neater than the original solution and tons faster too
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply