D Data Select on Julian Date Fields
This appendix contains these topics:
-
Section D.2, "Solution for example 1 - Data Select Blank Dates,"
-
Section D.3, "Solution for example 2 - Compare two dates in Data Selection."
Two common problems in data selection are trying to select records where a date field is blank or trying to compare two date fields. This document explains how to set up data selection for both of these situations.
D.1 Julian Date Examples
The following two examples are used to illustrate the solutions to two common problems.
D.1.1 Example 1
Find records in F060116 where the original date of employment field is blank. File: F060116, Field: YADSI
D.1.2 Example 2
Find records in F0411 where the G/L date is not equal to the invoice date. File: F0411, Fields: RPDGJ, RPDIVJ
Because of the editing associated with Julian date fields, the file fields cannot be used for data selection. Error 0004 - Date Invalid or Missing - is received. A calculated field needs to be created and used in place of the file fields. The following cover pages show how to setup the World Writer.
For information on how to create a calculated field, see Chapter 5, "Operands for Calculations" in the JD Edwards World World Writer Guide
D.2 Solution for example 1 - Data Select Blank Dates
D.2.1 Query Cover Page
Figure D-1 Query Version Cover Page screen
Description of "Figure D-1 Query Version Cover Page screen"
The calculated field is named ORIGINAL. The expression for the calculated field is DIGITS(YADSI). YADSI is the file field name. This field does not need to be a printed field.
D.2.2 Data Selection
Usually date fields that display as blank on a screen actually contain zeros in the physical file. On the Data Selection screen, press F4 to open the fold area and type the calculated field name in the fold area of a blank line. Type EQ in the Test field and '000000' in the Value field. In this example the Test is EQ (equal to); any single selection tests can be used, i.e. NE, LE, LT, NL, GE, GT, NG.
If this does not produce the desired results, the field may actually contain blanks instead of zeros. Change the Data Selection as shown below to retrieve blank values.
Figure D-3 Data Selection (With Alternate Values) screen
Description of "Figure D-3 Data Selection (With Alternate Values) screen"
D.3 Solution for example 2 - Compare two dates in Data Selection
D.3.1 Query Cover Page
Figure D-4 Query Version Cover Page screen
Description of "Figure D-4 Query Version Cover Page screen"
The calculated fields are named ##DIVJ and ##DGJ. The expressions for the calculated fields are the date field names from the file, RPDIVJ and RPDGJ. These fields do not need to be printed fields.
D.3.2 Data Selection
On the Data Selection screen, press F4 to open the fold area. On a blank line, type one of the calculated field names in the fold area. Type NE in the Test field and the other calculated field name in the Value field. In this example the Test is NE (not equal to); any single selection tests can be used, i.e. NE, LE, LT, NL, GE, GT, NG.
Figure D-5 Data Selection (Fold area) screen
Description of "Figure D-5 Data Selection (Fold area) screen"