Change sub query variable based upon sub query

Question

Post reply

Change sub query variable based upon sub query

thunderousity

Default port

Points: 1466
More actions
July 29, 2013 at 3:27 am

#395216

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.
SET @tol = 10;
SET @trig = 300;
SELECT EditionID, Make, Model, EditionDesc, Value1
FROM t1
HAVING Value1
BETWEEN
(SELECT Value1, TRUNCATE(Value1-@tol) as Value1min
FROM t1 WHERE EditionID = 10) iTable1)
AND
(SELECT Value1, TRUNCATE(Value1+@tol) as Value1max
FROM t1 WHERE EditionID = 10) iTable2)
ORDER BY Value1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure I'm getting this correct, but based on your description, this is my guess.

There are some errors on the code, I changed to what seemed correct.

--Look for these expressions

--TRUNCATE(Value1-CASE WHEN Value1 >= @trig THEN @tol* 2 ELSE @tol END)

SET @tol = 10;

SET @trig = 300;

SELECT EditionID

,Make

,Model

,EditionDesc

,Value1

FROM t1

WHERE Value1 BETWEEN (

SELECT TRUNCATE (Value1 - CASE WHEN Value1 >= @trig THEN @tol * 2 ELSE @tol END) AS Value1min

FROM t1

WHERE EditionID = 10

)

AND (

SELECT TRUNCATE (Value1 + CASE WHEN Value1 >= @trig THEN @tol * 2 ELSE @tol END) AS Value1max

FROM t1

WHERE EditionID = 10

)

ORDER BY Value1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

thunderousity Default port Points: 1466 More actions · Answer 2

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Duplicate thread.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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