E9.2 Set selection question - how to use OR

[Dana BY]

I know this has to be really stupid, but I am really lost on how to use this Set User Selection. Sometime ago I've seen this described on one blog, but unfortunately I didn't save it anywhere.
So, my question is how to use OR in Set Selection commands set?
I need for my app to select data based on query "Select * from GRID where AN8 = ER_Variable, UPMJ = DateToday, IDLN = 1 and BKTP is in ("1", "B")
How do I use set selection there?
Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "1", <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "B", <Or>)

Will it be right? Because when I tried it something went wrong

 

[ccornagg]

I know this has to be really stupid, but I am really lost on how to use this Set User Selection. Sometime ago I've seen this described on one blog, but unfortunately I didn't save it anywhere.
So, my question is how to use OR in Set Selection commands set?
I need for my app to select data based on query "Select * from GRID where AN8 = ER_Variable, UPMJ = DateToday, IDLN = 1 and BKTP is in ("1", "B")
How do I use set selection there?
Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "1", <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "B", <Or>)

Will it be right? Because when I tried it something went wrong

Hi Dana,

you need to follow the same rule for standard data selection:
since we cannot use parentheses we need to double the first part of the statement.

Something like that:

Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "1", <And>)
Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <Or>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "B", <And>)

If you activate tracelog you can get how parentheses are automatically added with OR statement.

Kind regards,

Carlo

 

[BOster]

There is also the system function called "Set Selection Group" that tries, and fails miserably, to replicate the functionality you get with the C JDEBASE api JDB_SetSelectionX to allow setting parentheses around different parts of the WHERE clause. For EXTREMELY simple cases you may get "Set Selection Group" to work. Its worth a shot but I STRONGLY advise you to review the resulting SQL in the JAS debug log.

 

[JohnDanter2]

Hi

Look for this
E1: RDA: Event Rule System Functions to Modify Data Selection and Data Sequence (Doc ID 1488886.1)

If you want to write ER that does this: A and B Or A and C
It would appear like this

SS = A none
SS = B AND

SS = A OR
SS = C AND

Odd, but it looks at what’s come before not what’s to come???

So your code.....
Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "1", <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "B", <Or>)

E1 will do this

F0101 AN8 = ER Var AND UPMJ = today AND IDLN = 0 AND BKTP = 1
OR BKTP = B

So you'll get ALL records on F0101 where BKTP = B regardless of the other things you're filtering on.
I suspect you need this

Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "1", <And>)

Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, "B", <Or>)
Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)


And/Or Valid values are : , , Specify whether the selection should be treated as an And condition or an Or condition is used on the first line of the Set User Selection criteria when the selection criteria is replacing the existing section data selection

Maybe add those BKTP values to a UDC and create a loop as you read through them, then you only need to code the code once

Get UDC
While SV File IO = success
Set Selection(FC Grid, "F0030", "BKTP", <Equal To>, UDC KY BKTP, <Or>)
Set Selection(FC Grid, F0101, "AN8", <Equal To>, ER_Variable, <And>)
Set Selection(FC Grid, F0101, "UPMJ", <Equal To>, DateToday, <And>)
Set Selection(FC Grid, "F0111", "IDLN", <Equal To>, <Zero>, <And>)
End While

Also, the SQL will be like this

WHERE ( ( Version Data Selection ) (First Event Rule Set User Selection <AND>/<OR>/<NONE>=<AND>) ( Event Rule Set User Selection ) ) AND ( JOIN ) AND (Row Security)​

 

[Dana BY]

Thank you all very much!!!