This section covers the following topics:
Files are groups of data stored together and treated as one unit. Files contain records which in turn contain fields. The diagram below gives an overview of file structure:
Super Natural can access the following types of files:
The data stored in a database is grouped together in files. Database files are defined by the database administrator. Only users who are authorized to use data maintenance transactions can alter the contents of database files.
The following demonstration files are supplied with Super Natural so you can reproduce the examples shown in this documentation:
SAG-TOURS-E-CH-PRICES
SAG-TOURS-E-COMPANY
SAG-TOURS-E-CONTRACT
SAG-TOURS-E-CRUISE
SAG-TOURS-E-PERSON
SAG-TOURS-E-YACHT
User files are defined by users. Only the creator and the administrator can perform maintenance on a user file. For more information on user files, see Working with User Files.
PC files are sequential files stored in a PC. You can read data from and write data to a PC file using the transaction modes Data Selection PC FILE and Destination PC FILE respectively.
Work files are sequential files stored in the mainframe database. You can read data from and write data to a work file using the transaction modes Data Selection WORK FILE and Destination WORK FILE respectively.
Superfiles are a special type of file and are described later in this section.
If you want to use fields from more than one files you can use the
INCLUDE command from any point within a transaction
to define a link to a secondary file, file lookups, and the order in which file
lookups are to be processed.
Sections later in this section give detailed technical information on what actually happens when you link files and perform file lookups. The sample transactions SAMPLE6 and SAMPLE7 described in the Tutorial give examples of file linking and file lookup.
To include other files in a transaction
Issue the INCLUDE command.
The Include Files window appears:
17:55 ***** Super Natural ***** 04.Jan.1998
SN3011 - Worksheet - Monday
Ref DB Field Name +--------------------Include Files--------------------+
AA 1K YACHT-ID .... ! !
AB 1K YACHT-NAME .. ! Primary File ..... SAG-TOURS-E-CRUISE______________ !
AC 1 YACHT-BRANCH ! Secondary File ... ________________________________ !
AD 1K ID-CH-BASE .. ! !
AE 1K ID-S-OWNER .. ! Add Lookup File .. ________________________________ !
AF 1K DID-DESCRIPTI ! Process Lookups .. _ !
AG 1K YACHT-TYPE .. ! !
AH 1 LENGTH ...... +-----------------------------------------------------+
|
From the Include Files window, you can do the following:
Redefine the primary file
Define file link
Define one or more file lookups
Process lookups
The Primary File field in the Include Files window shows which file you have defined as your primary file. If no fields from the primary file are in use, you can enter another file name here, otherwise the field is protected.
The file to which you link the primary file is known as the secondary file.
To link the primary file to another file
Enter the name of the secondary file in the Secondary File field in the Include Files window.
The Define File Link window appears as in the following example:
17:31 ***** Super Natural ***** 1998-05-13
SAMPLE6 - Worksheet - SN3011
Ref DB Field Name +--------------- +----------Define File Link----------+
BE _____________ ! ! !
BF _____________ ! Primary File . ! Primary File !
BG _____________ ! Secondary File ! SAG-TOURS-E-CRUISE !
BH _____________ ! ! Link field in primary file !
BI _____________ ! Add Lookup Fil ! id-yacht________________________ !
BJ _____________ ! Process Lookup ! From position .. ... !
BK _____________ ! ! to position .. ... !
BL _____________ +--------------- ! !
BM ______________________________ ! Secondary File !
BN ______________________________ ! SAG-TOURS-E-YACHT !
BO ______________________________ ! Link field in secondary file !
BP ______________________________ ! yacht-id________________________ !
BQ ______________________________ ! !
BR ______________________________ +------------------------------------+
|
To specify the Primary File
Type the name of the link field in the primary file in the Link Field In Primary File field. The Primary File field shows the name of the primary file.
Or:
Enter an asterisk (*) in the
Primary File Link field to obtain the Field
Selection List window if you do not know the exact field name.
The fields which already appear in theFieldname column of the Worksheet are listed first.
Mark the required field.
Press Enter.
The field name appears in the Link Field In Primary File field.
To specify the Position
Enter the position number of the first part of the field you want to use in the From Position field.
Enter the position number of the last part of the field you want to use in the To Position field.
You can also just use a part of the field in the primary file for linking purposes (optional). For example, you may want to use the values in the second, third and fourth positions of the field.
To specify the Secondary File
Enter the name of the link field from the secondary file in the Link Field In Secondary File field. The Secondary File field shows the name of the secondary file.
If you have already defined a secondary file, the Secondary File field in the Include Files window shows which file you have defined. If no fields from the secondary file are in use, you can enter another file name here, otherwise the field is protected.
Or:
Enter an asterisk (*) to obtain a list of
the fields you may use.
Only fields with the same format as the primary file link field are listed.
The link field in the secondary file must have a logical connection with the primary file link field. If the link field in the secondary file is longer than the link field in the primary file, all records in the secondary file which begin with the value of the primary file link field will be selected.
Mark the required field.
Press Enter.
The field name appears in the Link Field In Secondary File field.
When you have completed your entries in the Define File Link window, press Enter.
The Include Files window reappears.
The file in which you want to look up data is known as the lookup file.
To perform a lookup to another file
Enter the name of the lookup file in the Add Lookup File field in the Include Files window.
The Define File Lookup window appears:
11:07 ***** Super Natural ***** 05.Jan.1998
SN3011 - Worksheet - Tuesday
Ref DB Field Name +- +----------------Define File Lookup----------------+
AA 1K YACHT-ID .... ! ! !
AB 1K YACHT-NAME .. ! ! Lookup link field !
AC 1 YACHT-BRANCH ! ! ________________________________ !
AD 1K ID-CH-BASE .. ! ! Lookup File !
AE 1K ID-S-OWNER .. ! ! SAG-TOURS-E-CONTRACT !
AF 1K DID-DESCRIPTI ! ! Lookup field in lookup file !
AG 1K YACHT-TYPE .. ! ! ________________________________ !
AH 1 LENGTH ...... +- ! !
AI 1 WIDTH .......... ! Lookup (1:1) ...................... Y (Y/N) !
AJ 1 DRAFT .......... ! or enter N for lookup (1:n) !
AK 1 SAIL-SURFACE ... ! Process even if no records found .. Y (Y/N) !
AL 1 MOTOR .......... ! Search position ................... 3 (3 - 99) !
AM 1 HEAD-ROOM ...... ! !
AN 1 BUNKS .......... ! Select lookup display fields ...... X !
! !
+--------------------------------------------------+
|
Enter the name of the field in the primary file (or secondary file if one is defined) via which you want to perform the file lookup in the Lookup Link Field field.
Or:
Enter an asterisk (*) to obtain a list of
the fields you may use.
The fields which already appear in the Fieldname column of the Worksheet are listed first.
Enter the name of the field in the lookup file via which you want to perform the file lookup in the Lookup Link Field field.
Or:
Enter an asterisk (*) to obtain a list of
the fields you may use.
Only fields with the same format as the lookup field in the primary or secondary file are listed.
The lookup field in the primary or secondary file must have a logical connection with the lookup field in the lookup file.
Mark the required field.
Press Enter.
The field name appears in the Lookup Link Field field.
Enter "N" in the Lookup (1:1) field to perform a lookup (1:n).
The default lookup type is (1:1), but you can also perform a lookup of the type (1:n)
To process Even If No Records Found
Enter "N" in the Process Even If No Records Found field to break off processing of the lookup file if no records are found.
By default, lookup processing takes place even if no records are found in the lookup file. You can choose to break off processing of the lookup file if no records are found. This means that only the records in the primary file for which a record with a corresponding link field value is found in the lookup file are available.
Enter another number (from 3 - 99) in the Search Position field if you want to change the default search position.
The search position determines the order in which lookups are processed. The significance of this option is explained in the section What is File Lookup. The default search position is 3 for the first lookup you define, 4 for the second etc.
To Select Lookup Display Fields
Enter any non-blank character in the Select Lookup Display Fields.
You can define which fields from the lookup file you want to display in the report. A window containing a selection list of the fields in the lookup file which you may display appears.
Mark the fields you want to display.
Press Enter.
You are returned to the Define File Lookup/Link window.
When you have finished making your entries in the Define File Lookup/Link window, press Enter.
You are returned to the Include Files window. The number of lookup files currently defined is shown next to the Process Lookups field.
The number after the Process Lookups field in the Include Files window indicates how many file lookups have been defined for the current transaction. If no lookups have been defined, the Process Lookups field is protected.
To obtain a list of the lookup files currently in use
Mark the Process Lookups field with any non-blank character.
The Defined Lookup Files window appears as in the following example:
13:20 ***** Super Natural ***** 05.Jan.1998
SN3011 - Worksheet - Tuesday
Ref DB Field Name +------ +------------Defined Lookup Files-------------+
AA 1K YACHT-ID .... ! ! !
AB 1K YACHT-NAME .. ! Prima ! Cmd Lookup File Seq !
AC 1 YACHT-BRANCH ! Secon ! __ SAG-TOURS-E-CONTRACT 3 !
AD 1K ID-CH-BASE .. ! ! __ !
AE 1K ID-S-OWNER .. ! Add L ! __ !
AF 1K DID-DESCRIPTI ! Proce ! __ !
AG 1K YACHT-TYPE .. ! ! __ !
AH 1 LENGTH ...... +------ ! __ !
AI 1 WIDTH ............... ! __ !
AJ 1 DRAFT ............... ! __ !
AK 1 SAIL-SURFACE ........ ! __ !
AL 1 MOTOR ............... ! __ !
AM 1 HEAD-ROOM ........... ! !
AN 1 BUNKS ............... +---------------------------------------------+
|
You can either modify or delete the definitions for each lookup file.
To modify the lookup definition for a lookup file
Enter the command abbreviation MO in the
command column next it.
The Define File/Lookup Link window appears where you can redefine the lookup. The names of the lookup fields in both the lookup file and the primary or secondary file are replaced with the field references.
You cannot change the lookup fields or lookup file.
To delete the lookup definitions for a file
Enter the command abbreviation DE in the
command column next to it.
With Super Natural Version 3.1.1, you can use fields as lookup fields more than once. If you do this, you cannot subsequently change the search sequence.
The section Sample 6: File Link in the Tutorial gives an example of a transaction using file link.
When you link files, Super Natural generates a search on two levels when the transaction is run.
A search is made of the primary file for all records which meet the selection criteria. A set of records is found as in the following example:
In this example, LETTER is a field in the primary file and NUMBER is a field in the secondary file.
When a record is found in the primary file, Super Natural takes the value of its link field and searches the secondary file for all records whose link field has the same value:
When the records from the primary and secondary files are joined via the link field, any selection criteria using fields from the secondary file are applied.
The records found in the primary file and the records in the secondary file with a corresponding link field value which also fulfil the selection criteria are returned:
The Sample 7: File Lookup in the Tutorial gives an example of a transaction using file lookup.
There are two types of file lookup:
Lookup 1:1
Lookup 1:n
The lookup link field can be one of the following:
Field in the primary file
Field in the secondary file
Field in a lookup file
User field
The Lookup Link field is used to provide the link between the file in which it is contained and the lookup file via the Lookup field. The Lookup Link field and lookup field must be of like format and represent a logical relationship.
Fields contained in lookup files cannot be used as further selection
criteria. However, you can use fields from the lookup file with the
ACCEPT and REJECT
keywords in a THEN clause in the Logical Conditions Editor.
You can define multiple lookups using more than one link field, but you can only access one file per link field.
When performing a file lookup (1:1), the relationship between link field and records returned can be expressed by the ratio (1:1). This means that if the link field has the same value for n records in the lookup file, only the first record found is returned for the report.
Only one record from the lookup file can be returned for each record in the primary file.
When you perform a file lookup (1:1), Super Natural generates a multiple-level search when the transaction is run: a two-level search if you are only using a primary file, or a three-level search if you are using both a primary and a secondary file.
When you run a transaction using a primary file and a lookup file (lookup 1:1), the following takes place:
Super Natural searches the primary file for records that meet the selection criteria you have specified. A set of records is selected as in the following example:
When a record is found in the primary file, Super Natural takes the value of its lookup link field and searches the lookup file for the first record whose lookup field has the same value.
The diagram on the following page shows an example of a lookup file search.
Compare this diagram with the diagram File Link Processing - Secondary File Search in the section What is File Linking earlier in this section.
The records found in the primary file and the records in the lookup file are returned and can be displayed in the report:
A search is made of the primary file for all records which meet the selection criteria.
For each record selected in the primary file, Super Natural searches the secondary file for each record where the value of the Link field in the secondary file is the same as that of the Link field in the primary file in the record currently being processed. The selection criteria are applied to the records found in the secondary file.
For each record selected in the secondary file, Super Natural performs a search in the lookup file for the first record in which the value of the lookup field is the same as that of the lookup field in the secondary file record being processed.
The records found in the primary file, the records found in the lookup file and the records in the secondary file with a corresponding lookup field value are returned for the report.
Using lookup (1:n) you can link primary and secondary files to multiple files. When performing a file lookup (1:n), the relationship between lookup field and records returned can be expressed by the ratio (1:n). This means that if the lookup field has the same value for n records in the linked file, n records are returned for the report.
The only difference between lookup (1:n) and file link, is that you cannot use the fields in the lookup file(s) as part of the transaction's selection criteria.
If you have entered "N" in the Process Even If No Records Found field, processing of the lookup file is broken off if no record is found to match a record selected in the primary file (or secondary file if defined). The record from the primary file (or secondary file if defined) is no longer available to you.
This means that only the records in the primary file (or secondary file if defined) for which a record with a corresponding lookup field value is found in the lookup file are returned.
You use the transaction option Processing Sequence to determine the order in which the functions File Lookup (F), Calculations (C), Logical Conditions (L), Interfield Arithmetic (A), External Sorting (S) and Updating Records (U) are processed when a transaction is run. The default order is FCLASU. For further information on transaction options see Adjusting a User Profile.
When you run a transaction with Process Even if No
Records Found = NO, the functions determined after
("F") in the processing sequence are not performed for a
record found in the primary file if an equivalent record in the lookup file is
not found.
The following diagram shows which values are returned by the example earlier in this section if Process Even If No Records Found is set to NO:
Compare this diagram with the diagram File Lookup Processing - Records Returned for Report earlier in this section.
Superfiles are transactions created by the administrator which you can use instead of a file. Superfiles are ready-made transactions or transaction templates which you can use as a basis for your own transactions. Superfiles are often transactions in which files are already linked and complicated or lengthy selection criteria, logical condition processing statements or calculations are already entered. You can take the superfile and then adapt it to your own needs. The aim of superfiles is to save you time.
To use a superfile
Enter its name in the File Name field in the Add Transaction window
Or:
Select it from the File Selection List.
The Worksheet appears with entries already made in it. You can now modify the transaction as described below.
You can modify the following when using a superfile:
Sort and control fields
Display fields
System functions
Selection criteria
Logical conditions
SQL-SELECT
Calculations
Modifications which you make to a superfile are only valid for the transaction you make them in. You cannot modify the superfile itself.
You cannot modify the following when using a superfile:
Files used
Fields used
File specifications
Super Natural provides access to extended file descriptions and file comments stored in the Software AG data dictionary Predict. (This feature is only available at sites where Predict is installed.)
You can access extended file information by marking the Information column of any File Selection List window, for example, when adding a user file or a transaction.