Template:AskPlus E.8.4.5

From Vital Soft Wiki
Jump to: navigation, search

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.
Retrieved from "https://wiki.vital-soft.com/index.php?title=Template:AskPlus_E.8.4.5&oldid=2707"