Skip to Content
avatar image
Former Member

Edit to exisiting number query

Hello All,


I need some help on the below query, right now it's showing everything within a fixed 10% of the Variable data that the user enters.


I need the fixed 10% to become a variable also, so i can say i want to see within 50% or 47% or any random %


/* SELECT FROM OITM P1 */

DECLARE @RANGE1 AS DECIMAL(10 ,3)

/* WHERE */

SET @RANGE1 = /* P1.U_Attribute1 */ [%1]

/* SELECT FROM OITM P2 */

DECLARE @RANGE2 AS DECIMAL(10 ,3)

/* WHERE */

SET @RANGE2 = /* P2.U_Attribute2 */ [%2]

/* SELECT FROM OITM P3 */

DECLARE @RANGE3 AS DECIMAL(10 ,3)

/* WHERE */

SET @RANGE3 = /* P3.U_Attribute3 */ [%3]

SELECT T0.[ItemCode], T0.frgnname, T0.[U_Attribute1], T0.[U_Attribute2], T0.[U_Attribute3], T0.[U_Attribute4] FROM OITM T0

WHERE U_Attribute1 BETWEEN @RANGE1*0.9 AND @RANGE1*1.1

AND U_Attribute2 BETWEEN @RANGE2*0.9 AND @RANGE2*1.1

AND U_Attribute3 BETWEEN @RANGE3*0.9 AND @RANGE3*1.1

Is this something somebody can help me out with?

cheers

Jamie

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 19, 2015 at 12:38 PM

    Hi Jamie,

    The challenge is that parameters in B1 will only work if they are directly related to a field. In your case the original variables are related to the fields U_Attribute1, U_Attribute2 and U_Attribute3.

    Hence the variable declaration syntax.

    Changing the percentages to variables will be tricky because they are not represented by fields.

    Good news is, you can make this work in a two different ways (user friendly or lazy developer friendly):

    1. Create a user defined table, with one user defined field with the same settings as the U_Attribute fields. Then in your query, use this user defined table to "link" your variable to an existing field.
    2. Remove all the fancy sql to B1 parameter conversion and add a couple of variables. All variables will have to be filled in by editing the query.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded