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:
•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:
<Tablename.columnname> BETWEEN <literal1> AND <literal2> <Tablename.columnname> NOT BETWEEN <literal1> AND <literal2>
•To compare a field in a JD Edwards EnterpriseOne table with a set of values, use one of the following syntaxes:
<Tablename.columnname> IN (literal1,literal2,literal3, …..) <Tablename.columnname> NOT IN (literal1,literal2,literal3, …..)
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.:
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 SQL ELT optimization when you use data filters with expressions that contain the OR or to_date conditions.