Edit to exisiting number query

[Former Member]

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

[Johan_H · Accepted solution]

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

[Former Member · Accepted solution]

Johan,

I'm a little bit confused by the following:

How will creating a UDT and linking it to this query help me out at all? As I still won't have the functionality to be able to manually type into a variable field. So i will still be stuck to what percentage I can search by.

Basically I want to be able to say the following:

Attribute 1 = 200 and I want to see anything within x% of this (So x% below, x% above)

Attribute 2 = 300 and I want to see anything within x% of this (so x% below, x% above)

Attribute 3 = 400 and I want to see anything within x% of this (so x% below, x% above)

But I want to be able to say

attribute 1 - 23%

Attribute 2 - 47%

Attribute 3 - 18%

So basically have complete control over the percentages

[Johan_H · Accepted solution]

Hi Jamie,

Did I understand correctly that you want this:

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

to work like this ?:

WHERE U_Attribute1 BETWEEN @RANGE1*@PERC1 AND @RANGE1*@PERC2

AND U_Attribute2 BETWEEN @RANGE2*@PERC1 AND @RANGE2*@PERC2

AND U_Attribute3 BETWEEN @RANGE3*@PERC1 AND @RANGE3*@PERC2

Regards,

Johan

[Former Member · Accepted solution]

Johan,

you are correct, this is exactly how I need to see this query

cheers

Jamie

[Johan_H · Accepted solution]

Hi Jamie,

Ok, so as I explained, variables in B1 queries, only work when they are directly related to some field in the database.

For these @PERC1 and @PERC2 variables there are no related fields in the database, so they will not work just like that.

The only purpose of the user table, is to give the B1 sql parser engine a database field to relate the variable to.

So in theory, with a user table you could declare these @PERC variables the same way as the others:

/* SELECT FROM [@YOURUSERTABLE] PR1 */

DECLARE @PR1 AS DECIMAL(10 ,3)

/* WHERE */

SET @PR1 = /* PR1.U_SomeFieldSimilarToU_Attribute1 */ [%4]

etc.

Please note, my system (version 8.8) is not very good with these kinds of variables yet, so I could not test it.

Also, I am not sure, but you may have to fill the user table with all possible values from 1 to 100. Don't worry, if this is needed you can do so with direct sql insert statements.

Regards,

Johan

[Johan_H · Accepted solution]

I just did a little test, and you might even be able to do this without a user table. You can test this trick with any system table and field that has the correct data type that would allow you to to write 1.1, etc.

[Former Member · Accepted solution]

Johan,

am I correct in saying that you want me to do the following:

create a UDT with exactly the same data type as the attribute fields

add in a load of data so 0-100 then link this field up to the percentage field as declared variables?

cheers

Jamie

[Johan_H · Accepted solution]

Hi Jamie,

That is pretty much it, yes.

However, in my own test, I could enter values that were not in the given field. So you may not have to fill anything into the table, or maybe just only one or two rows.

Also, please see my other answer below.

regards,

Johan

[Former Member · Accepted solution]

Johan,

Couldn't thank you enough. This really helped me out and pointed me towards getting the job done

cheers

Jamie

[Johan_H · Accepted solution]

Hi Jamie,

You are quite welcome 

Regards,

Johan