|
In this section: |
There are two ways to preserve virtual fields during join parsing. One way is to use KEEPDEFINES, and the second is to use DEFINE FILE SAVE and DEFINE FILE RETURN.
|
How to: |
|
Reference: |
The KEEPDEFINES parameter determines if a virtual field created by the DEFINE command for a host or joined structure is retained or cleared after the JOIN command is run. It applies when the DEFINE command precedes the JOIN command.
The prior virtual fields constitute what is called a context. Each new context creates a new layer or command environment. When you first enter the new environment, all virtual fields defined in the previous layer are available in the new layer. Overwriting or clearing a virtual field definition affects only the current layer. When you return to the previous layer, its virtual field definitions are intact.
New DEFINE fields issued after the JOIN command constitute another context, and by so doing generate a stack of contexts. In each context, all virtual fields of all prior contexts are accessible.
A JOIN CLEAR as_name command removes all the contexts that were created after the JOIN as_name was issued.
For DEFINE-based conditional joins, the KEEPDEFINES setting must be ON. You then must create all virtual fields before issuing the DEFINE-based conditional JOIN command. This differs from traditional DEFINE-based joins in which the virtual field is created after the JOIN command. In addition, a virtual field may be part of the JOIN syntax or WHERE syntax.
DEFINE commands issued after the JOIN command do not replace or clear the virtual fields created before the join, since a new file context is created.
SET KEEPDEFINES = {ON|OFF} where:
Virtual fields defined prior to setting KEEPDEFINES ON are not preserved after a JOIN command.
The first virtual field, DAYSKEPT, is defined prior to issuing any joins, but after setting KEEPDEFINES to ON. DAYSKEPT is the number of days between the return date and rental date for a videotape:
SET KEEPDEFINES = ON DEFINE FILE VIDEOTRK DAYSKEPT/I5 = RETURNDATE - TRANSDATE; END
The ? DEFINE query command shows that this is the only virtual field defined at this point:
? DEFINE
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK DAYSKEPT I5 4
The following request prints all transactions in which the number of days kept is two:
TABLE FILE VIDEOTRK PRINT MOVIECODE TRANSDATE RETURNDATE DAYSKEPT COMPUTE ACTUAL_DAYS/I2 = RETURNDATE-TRANSDATE; WHERE DAYSKEPT EQ 2 END
The first few lines of output show that each return date is two days after the transaction date:
MOVIECODE TRANSDATE RETURNDATE DAYSKEPT ACTUAL_DAYS --------- --------- ---------- -------- ----------- 001MCA 91/06/27 91/06/29 2 2 692PAR 91/06/27 91/06/29 2 2 259MGM 91/06/19 91/06/21 2 2
Now, the VIDEOTRK data source is joined to the MOVIES data source. The ? DEFINE query shows that the join did not clear the DAYSKEPT virtual field:
JOIN MOVIECODE IN VIDEOTRK TO ALL MOVIECODE IN MOVIES AS J1 ? DEFINE
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK DAYSKEPT I5 4
Next a new virtual field, YEARS, is defined for the join between VIDEOTRK and MOVIES:
DEFINE FILE VIDEOTRK YEARS/I5 = (TRANSDATE - RELDATE)/365; END
The ? DEFINE query shows that the virtual field created prior to the join was not cleared by this new virtual field because it was in a separate context:
? DEFINE
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK DAYSKEPT I5 4 VIDEOTRK YEARS I5 5
Next, the field DAYSKEPT is re-defined so that it is the number of actual days plus one:
DEFINE FILE VIDEOTRK DAYSKEPT/I5 = RETURNDATE - TRANSDATE + 1; END
The ? DEFINE query shows that there are two versions of the DAYSKEPT virtual field. However, YEARS was cleared because it was in the same context (after the join) as the new version of DAYSKEPT, and the DEFINE command did not specify the ADD option:
? DEFINE
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK DAYSKEPT I5 4 VIDEOTRK DAYSKEPT I5 4
The same request now uses the new definition for DAYSKEPT. Note that the number of days between the return date and transaction date is actually one day, not two because of the change in the definition of DAYSKEPT:
MOVIECODE TRANSDATE RETURNDATE DAYSKEPT ACTUAL_DAYS --------- --------- ---------- -------- ----------- 040ORI 91/06/20 91/06/21 2 1 505MGM 91/06/21 91/06/22 2 1 710VES 91/06/26 91/06/27 2 1
Now, J1 is cleared. The redefinition for DAYSKEPT is also cleared:
JOIN CLEAR J1 ? DEFINE
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK DAYSKEPT I5 4
The report output shows that the original definition for DAYSKEPT is now in effect:
MOVIECODE TRANSDATE RETURNDATE DAYSKEPT ACTUAL_DAYS --------- --------- ---------- -------- ----------- 001MCA 91/06/27 91/06/29 2 2 692PAR 91/06/27 91/06/29 2 2 259MGM 91/06/19 91/06/21 2 2
The DEFINE FILE SAVE command forms a new context for virtual fields, which can then be removed with DEFINE FILE RETURN. For details, see Creating Temporary Fields.
The following command enables you to preserve virtual fields within a file context:
SET KEEPDEFINES=ON
The following command defines virtual field A for the VIDEOTRK data source and places it in the current context:
DEFINE FILE VIDEOTRK A/A5='JAWS'; END
The following command creates a new context and saves virtual field B in this context:
DEFINE FILE VIDEOTRK SAVE B/A5='ROCKY'; END ? DEFINE
The output of the ? DEFINE query lists virtual fields A and B:
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK A A5 VIDEOTRK B A5
The following DEFINE command creates virtual field C. All previously defined virtual fields are cleared because the ADD option was not used in the DEFINE command:
DEFINE FILE VIDEOTRK C/A10='AIRPLANE'; END ? DEFINE
The output of the ? DEFINE query shows that C is the only virtual field defined:
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK C A10
The following JOIN command creates a new context. Because KEEPDEFINES is set to ON, virtual field C is not cleared by the JOIN command:
JOIN MOVIECODE IN VIDEOTRK TAG V1 TO MOVIECODE IN MOVIES TAG M1 AS J1 ? DEFINE
The output of the ? DEFINE query shows that field C is still defined:
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK C A10
The next DEFINE command creates virtual field D in the new context created by the JOIN command:
DEFINE FILE VIDEOTRK SAVE D/A10='TOY STORY'; END ? DEFINE
The output of the ? DEFINE query shows that virtual fields C and D are defined:
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK C A10 VIDEOTRK D A10
The DEFINE FILE RETURN command clears virtual field D created in the current context (after the JOIN):
DEFINE FILE VIDEOTRK RETURN END ? DEFINE
The output of the ? DEFINE query shows that virtual field D was cleared, but C is still defined:
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK C A10
The following DEFINE FILE RETURN command does not clear virtual field C because field C was not created using a DEFINE FILE SAVE command:
DEFINE FILE VIDEOTRK RETURN END ? DEFINE
The output of the ? DEFINE query shows that virtual field C is still defined:
FILE FIELD NAME FORMAT SEGMENT VIEW TYPE VIDEOTRK C A10
Note: DEFINE FILE RETURN is only activated when a DEFINE FILE SAVE is in effect.
The conditional JOIN command can reference any and all fields in the joined segment and any and all fields in the parent segment, or higher on the parent's path.
When active, these join expressions screen the segment on which they reside (the child or joined segment). That is, if no child segment passes the test defined by the expression, the join follows the rules of SET ALL=OFF, or SET ALL=ON when no child segment exists. Unlike WHERE phrases in TABLE commands, JOIN_WHERE screening does not automatically screen the parent segment when SET ALL=ON.
WHERE criteria take effect in a join only when a TABLE request reference is made to a cross-referenced segment or its children. If no such reference is made, the WHERE has no effect.
The AT attribute is used to link the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used simply as segment references.
Note: If no WHERE criteria are in effect, you receive a Cartesian product.