How Event Rule Dataselection Works in JDE Tools

*The article is rated Must read for Beginners/Novice

There has been many a confusion with JDE freshers, on how to use the Internal Data selection, or called as ER Data selection and how to use them.

Following are our assumptions for the scenarios (for REPORT DESIGN AID (RDA)) -

  • There’s a Processing Option which takes inputs of Date range (F4201.DRQJ) for internal data selection.

Scenario 1

Suppose we need to select all Sales orders (F4201) entered between (DRQJ=) 7/1/2011 and 7/31/2011

Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<AND>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  -
SELECT * FROM F4201 WHERE (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')


Scenario 2

Now, if the UBE already has an External Data selection of DCTO=’SI’,'SO’ our earlier data selection of DRQJ (in processing options) between 7/1/2011 and 7/31/2011.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),,'07/01/2011',)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  -
SELECT * FROM F4201 WHERE (DCTO IN ('SI','SO')) AND (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')

Explanation -
The Selection Append Flag specifies if to append the extra data selection after the External Data selection of the UBE. If this is set to NO, only the data select statements after that is applied.


Scenario 3

Let’s take a scenario where you have an external data selection of DCTO = ‘SF’.
Our situation demands that if the Processing option range is entered, then select those in DRQJ range and all the ‘SF’ orders.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<OR>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  -
SELECT * FROM F4201 WHERE (DCTO='SF') OR (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')

Explanation -
How did that work? The basic understanding how to form your AND / OR conditions is that, the conditions you put at the End of your statements is what is placed before that statement. In the above scenario we had the OR condition in the first statement. The condition is placed before the Set Selection statement.


Scenario 4

Let’s take a scenario where you have an external data selection of DRQJ in range of 01/01/2011 and 01/31/2011.
Our situation demands that if the Processing option range is entered, then include that DRQJ range too along with the external data selection.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<OR>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)

Generated SQL  -
SELECT * FROM F4201 WHERE (DRQJ BETWEEN '01/01/2011' AND '01/31/2011') OR (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011')

Explanation -
How did that work? The basic understanding how to form your AND / OR conditions is that, the conditions you put at the End of your statements is what is placed before that statement. In the above scenario we had the OR condition in the first statement. The condition is placed before the Set Selection statement.


Scenario 5

Now suppose the above same scenario of DRQJ in range of 01/01/2011 and 01/31/2011 was given in Processing options, and along with it you also want to select only the order types SI and SO (also set in processing options). We have a tricky situation here. the Issue being that JD Edwards does not support parenthesis or does not allow you to segregate the SQL where clause to prioritize the conditions.

Lets think of the following code if you feel its its correct.

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<AND>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SI',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SO',<OR>)

Generated SQL  -
SELECT * FROM F4201 WHERE (DRQJ>='07/01/2011' AND DRQJ<='07/31/2011' AND DCTO='SI' OR DCTO='SO')

Do you think this will work? NO it does not.
The OR condition at the end does not have a priority or parenthesis and so messes up the statement we want to build. So how do we tackle this?

This is how we do it in JD Edwards……!

Set Selection Append flag (Yes)
Set Selection (BC Date - Requested (DRQJ),<Greater Than and Equal To>,'07/01/2011',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SI',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SO',<OR>)
Set Selection (BC Date - Requested (DRQJ),<Less Than and Equal To>,'07/31/2011',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SI',<AND>)
Set Selection (BC Order Type (DCTO),<Equal To>,'SO',<OR>)

Generated SQL  -
SELECT * FROM F4201
WHERE (DRQJ >= '07/01/2011' AND DCTO='SI' OR DCTO='SO' AND
DRQJ <='07/31/2011' AND DCTO='SI' OR DCTO='SO')

Explanation -
Yes now, if you look at the SQL generated, you can see how the selection works. BTW, SQL conditions work from right to left. i.e. the right most condition executes first, followed by the conditions on its left.

Do go ahead and comment out with questions if any.

Leave a Reply