JD Edwards EnterpriseOne Sources
You can use a JD Edwards EnterpriseOne single object as a source in a Data Synchronization task or a Mapping Configuration task.
When you configure the advanced source properties, you configure properties specific to JD Edwards EnterpriseOne. The JD Edwards EnterpriseOne source object represents the tables and views used in the read operation. The tables can represent base tables or interface tables in JD Edwards EnterpriseOne. The tables and views are grouped by system codes.
Before you configure a source, you must establish a connection with JD Edwards EnterpriseOne Enterprise Server.
Sorting Records from JD Edwards EnterpriseOne Sources
You can sort the rows queried from JD Edwards EnterpriseOne source. The Secure Agent adds the ports to the ORDER BY clause in the default query. You can sort rows extracted from a JD Edwards EnterpriseOne source in ascending or descending order. If you do not enter any value, the Secure Agent does not sort data.
Joining Records from JD Edwards EnterpriseOne Source
You can join two JD Edwards EnterpriseOne sources. Select the JD Edwards EnterpriseOne source object you want to use, and click Create Relationship. You can specify the Join type to join the sources. Join enables only when you have multiple objects.
You can specify the following types of joins:
- •Inner Join. Returns the data from the selected source object and the related source object that meets the join condition.
- •Left Join. Performs a left outer join on JD Edwards EnterpriseOne tables on which the join is defined.
- •Right Join. Performs a right outer join on JD Edwards EnterpriseOne tables on which the join is defined.
- •Outer Join. Returns all data from the selected source object. Also returns data from the related source object that meets the join condition.
By default, tables are joined by the inner join.
Rules and Guidelines for Join Conditions
Use the following guidelines when you enter a join condition:
- •You can use only the Equals operator while configuring a join.
- •You cannot configure Right Join and Outer Join for JD Edwards Enterprise One objects in Data Synchronization tasks.
- •Ensure that the source columns used in a Join condition are mapped to the target fields in the Field Mapping tab.
Filtering Records from JD Edwards EnterpriseOne Sources
You can use a filter expression to reduce the number of rows that the Secure Agent reads from the source. You can use a basic or advanced filter expression to select specific records from a JD Edwards EnterpriseOne table or view.
To create a basic filter, select the source object, the column of the JD Edwards EnterpriseOne object on which you want to apply the filter condition, the operator to use to filter the records, and the literal value you specify to filter the JD Edwards EnterpriseOne objects.
To create an advanced expression, use the following syntax:
- • To compare a field in a JD Edwards EnterpriseOne table with any other field, use the following syntax:
<Tablename.columnname> <operator> <Tablename.columnname>
- •To compare a field in a JD Edwards EnterpriseOne table with a literal value, use the following syntax:
<Tablename.columnname> <operator> <literal>
- •To compare a field in a JD Edwards EnterpriseOne table with a range of values, use one of the following syntaxes:
- •To compare a field in a JD Edwards EnterpriseOne table with a set of values, use one of the following syntaxes:
You must specify the database table name as Tablename while specifying the filter condition to extract data from a table. You can use a period (.) to separate the table name and column name.
The following table describes the supported operators that you can use in basic and advanced filters:
Operator | Expression | Description |
|---|
< | basic, advanced | Extracts data where value of a field is lesser than the value of a literal or the other field. For example, F0101.AN8 < 100. |
> | basic, advanced | Extracts data where value of a field is greater than the value of a literal or the other field. For example, F0102.AN9 > F0104.AX5. |
= | basic, advanced | Extracts data where value of a field is equal to a literal or the other field. For example, F0102.AN9 = F0104.AX5. You can also compare strings using this operator. |
<= | basic, advanced | Extracts data where value of a field is lesser than or equal to the value of a literal or the other field. For example, F0102.AN9 <= 405. |
>= | basic, advanced | Extracts data where value of a field is greater than or equal to the value of a literal or the other field. For example, F0102.AN9 >= 208. |
!= | basic, advanced | Extracts data where value of a field is not equal to the value of a literal or the other field. For example, F0102.AN9 != 2435. You can also compare strings using this operator. |
AND, and | advanced | Extracts data that satisfies more than one filter condition. For example, use the following filter condition to extract data for the employees who stay in U.S. and whose salary is less than $200: [ F005.Location = ‘U.S.’ ] AND [ F005.SAL < 200 ] |
OR, or | advanced | Extracts data that satisfies any one of the specified filter conditions. For example, use the following filter condition to extract data for the employees who either stay in U.S. or U.K.: [ F005.Location = ‘U.S.’ ] OR [ F005.Location = ‘U.K.’ ] |
LIKE, like | advanced | Extracts the string values that match a particular pattern. For example, use the following filter condition to extract data of the employees whose names start with Ace: F1010.Empname LIKE ‘Ace%’ The LIKE operator is not case sensitive. |
BETWEEN..AND, between..and | advanced | Extracts data from a range of values. For example, use the following filter condition to extract data for those employees whose salary is between $200 and $500: F0001.SAL BETWEEN 200 AND 500 The filter condition is equivalent to [ F0001.SAL >= 200 ] AND [ F0001.SAL <= 500 ] |
NOT BETWEEN ..AND, not between.. and | advanced | Extracts data where the value of a field is not from a range of values. For example, use the following filter condition to extract data for those employees whose salary is not between $100 and $200: F0001.SAL NOT BETWEEN 100 AND 200 |
IN, in | advanced | Extracts data where value of a field is a member of a set of valid values. For example, use the following filter condition to extract all the rows where value of AN12 is 101, 102, or 103: F0001.AN12 IN (101, 102, 103) |
NOT..IN, not..in | advanced | Extracts data where value of a field is not a member of a set of valid values. For example, use the following filter condition to extract all the rows where value of BN19 is not 101 or 102: F0001.AN12 NOT IN (101, 102) |
Rules and Guidelines for Filter Conditions
Use the following guidelines when you enter a filter condition:
- •The table name in a filter condition must be the name of the table imported from JD Edwards EnterpriseOne. If the filter is specified on a view, get the table name from the column name in the view source definition.
- •Enclose string values in single quotes.
- •Enter the literal value based on the data type of the column specified in the first token. For example,
F0101.AN8 < 100
F0101.ALPH LIKE ‘MARKETTING COMPANY’
- •Specify the literal value in the YYYY-MM-DD format if the data type is Jdedate. For example,
F0101.UPMJ = 2003-05-29
- •Specify the literal value in the YYYY-MM-DD HH:MM:SS format if the data type is Jdeutime. For example,
F01301.TDSTR = 2003-05-29 15:10:25
- •When you import a source object, the Secure Agent imports columns of the Jdetime data type as Math_Numeric. You need to change the data type to Jdetime. As you cannot edit the data type during a mapping, specify the literal value in the jdetime format in a filter condition. For example,
F0101_UPMT = 106675
- •Use the AND and OR operators to enter more than one filter condition.
- •Separate filter conditions by square brackets ([ ]).
- •Comparison operators have higher precedence than the logical operators AND and OR.
- •AND operator has higher precedence over the OR operator.
- •Use square brackets ([ ]) to change the precedence of operators.
- •You cannot apply the source filter on a binary field.
- •The basic filter does not support the Jdetime, Jdedate, and Jdeutime data types.
- •You cannot use pushdown optimization when you use data filters with expressions that contain the OR or to_date conditions.