Change sub query variable based upon sub query

  • 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

  • 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
  • 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

  • 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

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

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