Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question