Template:AskPlus E.8.4.5

Enhancements:

Outer Join:

• In version E.8.4.4 (and the E.8.4.3 Beta) the OUTER keyword was introduced to support outer joins in the FIND command. This was a global keyword that effected all sets used in the FIND.
• In version E.8.4.5 the syntax of the OUTSEL statement was enhanced so that the OUTER join logic could be applied to individual sets (rather than all sets).
• To specify an outer join in the OUTSEL, prefix the topic name with a plus sign as shown below.

Selection Syntax Using OUTER Keyword:

FIND PRIMARY=MEMBER-FILE;OUTSEL=(@)
OUTER
(LOAN-FILE.BALANCE > 100000 AND LOAN-FILE.REST-FLAG-11 <> 80 AND
 LOAN-FILE.SUFFIX = VEHICLE-INFO.SUFFIX)

Using the above syntax, because the OUTER applies to all secondary sets, all member-file records will be selected, even members that do not have qualifying loans. Of course this problem could be fixed by changing the primary to LOAN-FILE, but it can also be resolved using the following syntax.

Selection Syntax Using OUTSEL OUTER syntax:

FIND PRIMARY=MEMBER-FILE
OUTSEL=(MEMBER-FILE, LOAN-FILE, +VEHICLE-INFO)
(LOAN-FILE.BALANCE > 100000 AND LOAN-FILE.REST-FLAG-11 <> 80 AND
 LOAN-FILE.SUFFIX = VEHICLE-INFO.SUFFIX)

The plus sign that prefixes VEHICLE-INFO in the OUTSEL indicates that the outer logic should only apply to the join with VEHICLE-INFO. Therefore this selection will select members with qualifying loans, and it will select any matching VEHICLE-INFO records, however, no loans will be dropped when no matching VEHICLE-INFO record is found.

• New Special Operators:

• !recno(topic_name)

• Returns the current record number of the specified dataset or file.
• Spectrum uses the record number as a sub-sequence number in SH-HISTORY and LN-HISTORY.
• Example, !recno(HISTRY.SH-HISTORY)
• Works in both find, report and save, but probably only useful in report and save.
• Specifying !recno(field-name) is syntactically valid and equivalent to specifying the set or filename.

• !recwidth(topic_name)

• Returns the record width in bytes of the specified dataset or file.
• Example, !recno(HISTRY.SH-HISTORY)
• Works in find, report and save, but probably only useful in report and save.
• Specifying !recwidth(field-name) is syntactically valid and equivalent to specifying the set or filename.

• New System Variable:

• $rowno

• Returns the row number (starting from 1) of each record being read.
• The value of $ROWNO is equivalent to creating a numeric variable and adding one for each detail record.
• Works in find, report and save.