Defining Files

#### Search Results

Search

# Defining Files

This section covers the following topics:

- What are Files?

- Using More Than One File (The Command)

- What is File Linking?

- What is File Lookup?

- Superfiles

- Extended File Descriptions and File Comments

## What are Files?

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:

### File Types Used By Super Natural

Super Natural can access the following types of files:

#### Database 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

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

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

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

Superfiles are a special type of file and are described later in this section.

## Using More Than One File - The INCLUDE Command

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

### Redefining the Primary File

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.

### Linking Files

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

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

2. Mark the required field.

3. Press Enter.

   The field name appears in the Link Field In Primary File field.

To specify the Position

1. Enter the position number of the first part of the field you want to use in the From Position field.

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

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

2. Mark the required field.

3. Press Enter.

   The field name appears in the Link Field In Secondary File field.

4. When you have completed your entries in the Define File Link window, press Enter.

   The Include Files window reappears.

### Adding File Lookups

The file in which you want to look up data is known as the lookup file.

To perform a lookup to another file

1. 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            !
                                   !                                                  !
                                   +--------------------------------------------------+

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

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

4. Mark the required field.

5. Press Enter.

   The field name appears in the Lookup Link Field field.

To specify the Lookup (1:1)

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

To Search Position

- 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

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

2. Mark the fields you want to display.

3. Press Enter.

   You are returned to the Define File Lookup/Link window.

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

### Processing File Lookups

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.

## What is File Linking?

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.

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

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

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

## What is File Lookup?

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.

### Lookup

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.

#### Lookup from Primary File

When you run a transaction using a primary file and a lookup file (lookup 1:1), the following takes place:

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

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

1. The records found in the primary file and the records in the lookup file are returned and can be displayed in the report:

#### Lookup (1:1) from Primary and Secondary Files

1. A search is made of the primary file for all records which meet the selection criteria.

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

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

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

### Lookup (1:n)

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.

### Process Even if No Records Found = NO

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

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.

### Using Superfiles

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.

### What You Can Modify in a Transaction Using a Superfile

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.

### What You Cannot Modify in a Transaction Using a Superfile

You cannot modify the following when using a superfile:

- Files used

- Fields used

- File specifications

## Extended File Descriptions and File Comments

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.

### On this page

- What are Files?
- Using More Than One File - The INCLUDE Command
- What is File Linking?
- What is File Lookup?
- Superfiles
- Extended File Descriptions and File Comments