>Johno at 00:21, 13 April 2018

2018-04-13T00:21:09Z

>Johno: Created page with "'''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 ..."

2018-04-13T00:12:22Z

Created page with "'''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 ..."

New page

'''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.

← Older revision		Revision as of 00:21, 13 April 2018
Line 22:		Line 22:

	: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.		: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.

Template:AskPlus E.8.4.5 - Revision history

>Johno at 00:21, 13 April 2018

>Johno: Created page with "'''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 ..."