Preserving Virtual Fields During Join Parsing

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.

Preserving Virtual Fields Using KEEPDEFINES

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.

Syntax: How to Use KEEPDEFINES

SET KEEPDEFINES = {ON|OFF}

where:

ON
Retains the virtual field after a JOIN command is run.
OFF
Clears the virtual field after a JOIN command is run. This value is the default.

Reference: Usage Notes for KEEPDEFINES

Virtual fields defined prior to setting KEEPDEFINES ON are not preserved after a JOIN command.

Example: Preserving Virtual Fields During Join Parsing With KEEPDEFINES

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

Preserving Virtual Fields Using DEFINE FILE SAVE and RETURN

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.

Example: Preserving Virtual Fields With DEFINE FILE SAVE and RETURN

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.

Screening Segments With Conditional JOIN Expressions

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.

Parsing WHERE Criteria in a Join

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.