*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.

NiranjanDear Deepesh,

How are you Doing?

Im working with UBE to Formulate Sales Delivery Percentage on MTD Quantity.

For this I am extracting selected fields with calculated Date Difference between Order and Invoice Date

to Temp Table named(F59DTIME) based on Selection.

After that I am trying to calculate percentage based on subtotal by Each Date Difference & BussinessUNit over MTD Total Quanityby BusinessUnit using this table.

I did the above as shown below

VA rpt_Identifier2ndItem <-TK 2nd Item Number

0020 ! End While

0021 ! RV Day 02 = VA rpt_Day_Qty

0022 ! VA rpt_rQty02 = RV Day 02

0023 ! VA rpt_Qty02 = round(([VA rpt_rQty02]/[VA rpt_TotalMCUQuantity]),2)*100

0024 ! If VA rpt_Diff_Days is equal to "2"

0025 ! F59DTIME.Fetch Next

VA rpt_Identifier2ndItem <-TK 2nd Item Number

0026 ! If VA rpt_Diff_Days is equal to "2"

0027 ! While SV File_IO_Status is equal to CO SUCCESS

0028 ! VA rpt_Day_Qty = [VA rpt_Day_Qty]+[PC Quantity Shipped (F59DTIME) (SOQS)]

0029 ! F59DTIME.Fetch Next

VA rpt_Identifier2ndItem <-TK 2nd Item Number

0030 ! End While

0031 ! End If

0032 ! RV Day 03 = VA rpt_Day_Qty

0033 ! VA rpt_rQty03 = RV Day 03

0034 ! VA rpt_Qty03 = round(([VA rpt_rQty03]/[VA rpt_TotalMCUQuantity]),2)*100

0035 ! End If

0036 ! F59DTIME.Select

VA rpt_CostCenter = TK Business Unit -Home

VA rpt_Diff_Days = TK Period Number -Accounts Receivable

0037 ! If VA rpt_Diff_Days is equal to "3"

0038 ! F59DTIME.Fetch Next

PC Quantity Shipped (F59DTIME) <-TK Quantity Shipped

I Tried Debugging and i found Quantity per date difference is accumulating a constant Value inside While Loop.

Your valuable Support Really Appreciatable

Thanks & Regards

Rahul VijayHi Dipesh,

I am very new in JDE now I have below SQL statement that I want to put in E1 code can you help me?

NSERT INTO F55XXX SELECT SDMCU, SDITM, SDLITM, COUNT(SDITM) LKM, SUM(SDSOQS) KPL FROM F4211, F55365 WHERE SDMCU IN (‘ FCV’, ‘ FVA’, ‘ FTN’, ‘ FOU’, ‘ FCT’) AND SDSOQS > 0 AND SDDCTO IN (‘SA’,’SD’,’SO’,’SV’,’TC’,’TP’,’TU’,’T1′,’T8′,’SF’) AND SDADDJ BETWEEN TDPMB AND TDPME GROUP BY SDMCU, SDITM, SDLITM ;

Deepesh M DivakaranPost authorI assume this is a UBE where you are performing the above. You will heed to have a Section on F4211, F55365, with the dataselection as you need. Within the DO Section event of the Section, just insert the values into your required table.