Sample 7: File Lookup

#### Search Results

Search

# Sample 7: File Lookup

#### Task: List cruises starting in Korfu with their destination and starting date in order of starting date. List the type, name and length of the yachts which sail on these cruises.

Information on cruise starting harbor, starting date and cruise destination is stored in the file SAG-TOURS-E-CRUISE. Information on yacht length, type and name is stored in the file SAG-TOURS-E-YACHT. As each cruise can only have one value for destination, yacht length, name and type, you can take SAG-TOURS-E-CRUISE as the primary file and perform a lookup on the file SAG-TOURS-E-YACHT using the `INCLUDE` command.

For further information on file lookup, see INCLUDE command and What is File Lookup? in Defining Files in the User's Guide.

Super Natural searches the primary file and lookup file as follows when the transaction is run:

1. A search is made in the SAG-TOURS-E-CRUISE file for all records which meet the selection criterion START-HARBOR = 'KORFU'. The following records are found:

1. For each record found in the primary file, Super Natural searches the lookup file (SAG-TOURS-E-CRUISE) for the first record where the value of the lookup field (ID-YACHT) is the same as that of the link field in the primary file record. The following records are found:

1. The records found in the primary file are returned and the desired information from the corresponding records in the lookup file is displayed.

#### Transaction Specification

Primary File | SAG-TOURS-E-CRUISE
Lookup Field In Primary File | ID-YACHT
Lookup File | SAG-TOURS-E-YACHT
Lookup Field In Lookup File | YACHT-ID
Selection Criteria | START-HARBOR = 'KORFU'
Display Fields | 1: START-DATE 2: START-HARBOR 3: DESTINATION-HARBOR 4: YACHT-TYPE 5: YACHT-NAME 6: LENGTH
Sort Field | START-DATE

This section covers the following topics:

- Create the Transaction SAMPL7

## Create the Transaction SAMPL7

To Add a Transaction

1. Issue the `ADD TRANSACTION SAMPLE7` command.

   The Add Transaction window appears.

2. Type " File Lookup" in the Description field (optional).

To Choose a Primary File

The report requires data from the primary file SAG-TOURS-E-CRUISE.

1. Type SAG-TOURS-E-CRUISE in the File Name field.

2. Press Enter.

   The Worksheet appears.

To Choose Fields from Primary File

The report requires data stored in the primary file fields START-DATE, START-HARBOR and DESTINATION-HARBOR.

- Choose the fields as described in Step 3 of Sample 1.

To Choose Lookup File

1. Issue the `INCLUDE` command.

   The Include Files window appears.

2. Enter SAG-TOURS-E-YACHT in the Add File Lookup field. You can also use the `Asterisk (*) List` function.

        12:36                      ***** Super Natural *****                 1998-05-24
        SAMPLE7                          - Worksheet -                           SN3011

        Ref DB    Field Name    +--------------------Include Files--------------------+
        AE  1K 2  START-DATE .. !                                                     !
        AJ  1K    START-HARBOR  ! Primary File ..... SAG-TOURS-E-CRUISE______________ !
        AK  1K    DESTINATION-H ! Secondary File ... ________________________________ !
        BE        _____________ !                                                     !
        BF        _____________ ! Add Lookup File .. sag-tours-e-yacht_______________ !
        BG        _____________ ! Process Lookups .. _                                !
        BH        _____________ !                                                     !
        BI        _____________ +-----------------------------------------------------+

   The Define File Lookup window appears.

3. Type ID-YACHT in the Lookup Link Field field.

4. Type YACHT-ID in the Lookup Field in Lookup File field.

        12:36                      ***** Super Natural *****                 1998-05-24
        SAMPLE7                          - Worksheet -                           SN3011

        Ref DB    Field Name    +- +----------------Define File Lookup----------------+
        AE  1K 2  START-DATE .. !  !                                                  !
        AJ  1K    START-HARBOR  !  ! Lookup link field                                !
        AK  1K    DESTINATION-H !  !   id-yacht________________________               !
        BE        _____________ !  ! Lookup File                                      !
        BF        _____________ !  !   SAG-TOURS-E-YACHT                              !
        BG        _____________ !  ! Lookup field in lookup file                      !
        BH        _____________ !  !   yacht-id________________________               !
        BI        _____________ +- !                                                  !
        BJ        ________________ ! Lookup (1:1) ...................... Y   (Y/N)    !
        BK        ________________ !   or enter N for lookup (1:n)                    !
        BL        ________________ ! Process even if no records found .. Y   (Y/N)    !
        BM        ________________ ! Search position ...................   3 (3 - 99) !
        BN        ________________ !                                                  !
        BO        ________________ ! Select lookup display fields ...... X            !
                                   !                                                  !
                                   +--------------------------------------------------+

   The fields ID-YACHT and YACHT-ID are used as lookup fields because they both have the format `N8` and contain the same data. You can also use the `Asterisk (*) List` function to review the field list for each file.

5. Press Enter.

   Because the Select Lookup Display Fields field is marked by default, the Field Selection List window for the lookup file appears.

To Choose Fields from Lookup File

The report requires data stored in the lookup file fields YACHT-TYPE, YACHT-NAME and LENGTH.

1. Mark the fields in the Field Selection List window.

        12:36                      ***** Super Natural *****                 1998-05-24
        SAMPLE7                          - Worksheet -                           SN3011

        Ref DB    Field Name     +----------------Field Selection List----------------+
        AE  1K 2  START-DATE ..  !     Field Name                        Format  Info !
        AJ  1K    START-HARBOR   !  x  YACHT-NAME                        A30      _   !
        AK  1K    DESTINATION-H  !  _  YACHT-BRANCH                      A1       _   !
        BE        _____________  !  _  ID-CH-BASE                        A8       _   !
        BF        _____________  !  _  ID-S-OWNER                        N8       _   !
        BG        _____________  !  _  DID-DESCRIPTION                   A8       _   !
        BH        _____________  !  x  YACHT-TYPE                        A30      -   !
        BI        _____________  !  x  LENGTH                            P3.2     _   !
        BJ        ______________ !  _  WIDTH                             P3.2     _   !
        BK        ______________ !  _  DRAFT                             P3.2     _   !
        BL        ______________ !  _  SAIL-SURFACE                      P3       _   !
        BM        ______________ !  _  MOTOR                             P3       _   !
        BN        ______________ !  _  HEAD-ROOM                         P3.2     _   !
        BO        ______________ !  _  BUNKS                             P3       _   !
                                 !                                                    !
                                 +----------------------------------------------------+

2. Press Enter.

   The Field Selection List window closes.

3. Press Enter.

   The Define File Lookup window closes. The Include Files window now has a "1" next to the Process Lookups field to show that you have defined one lookup for the current transaction.

4. Press Enter.

5. The Worksheet reappears.

To Determine Display Sequence for Horizontal Output

1. Type "1" in the Sequence column next to the field START-DATE.

2. Type "2" in the Sequence column next to the field START-HARBOR.

3. Type" 3" in the Sequence column next to the field DESTINATION-HARBOR.

4. Type "4" in the Sequence column next to the field YACHT-TYPE.

5. Type "5" in the Sequence column next to the field YACHT-NAME.

6. Type "6" in the Sequence column next to the field LENGTH.

To Define Sort Criteria

The report requires that you sort the list according to cruise starting date.

1. Type "S1" in the Sort/Control (S/C) column next to the field START-DATE.

2. Press Enter.

   The Worksheet now has the following entries:

        13:22                      ***** Super Natural *****                 1998-05-24
        SAMPLE7                          - Worksheet -                           SN3011

        Ref DB    Field Name                         Seq   S/C  Func Def  Info Del
        AE  1K 2  START-DATE .....................   1__   S1_   __   __   __   __
        AJ  1K    START-HARBOR ...................   2__   ___   __   __   __   __
        AK  1K    DESTINATION-HARBOR .............   3__   ___   __   __   __   __
        AL  1K    ID-YACHT .......................   ___   ___   __   __   __   __
        BE  LK 3  YACHT-ID .......................   ___   ___   __   __   __   __
        BF  L  3  YACHT-NAME .....................   5__   ___   __   __   __   __
        BG  L  3  YACHT-TYPE .....................   4__   ___   __   __   __   __
        BH  L  3  LENGTH .........................   6__   ___   __   __   __   __
        BI        ________________________________   ___   ___   __   __   __   __
        BJ        ________________________________   ___   ___   __   __   __   __
        BK        ________________________________   ___   ___   __   __   __   __
        BL        ________________________________   ___   ___   __   __   __   __
        BM        ________________________________   ___   ___   __   __   __   __
        BN        ________________________________   ___   ___   __   __   __   __

        Fill in required definitions
        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help  Next  Exit  Field Incl  Flip  -     +           Sel   Run   Canc

   The 'L's in the Database (DB) column indicate that the fields next to them are in the lookup file.

   Note that the Sequence (Seq), Sort/Control (S/C), Function (Func) and Delete (Del) columns are protected for the field YACHT-ID which is the lookup field in the lookup file.

To Define Selection Criteria

1. Issue the `SELECTION` command. You can also abbreviate this command to `SEL`.

   The Selection Editor appears.

   The report requires information on cruises which start in Korfu.

2. Enter "START-HARBOR = 'KORFU'" in the editor area.

        13:23                      ***** Super Natural *****                 1998-05-24
        SAMPLE7                       - Selection Editor -                      SNZUL-S

              1  < START-HARBOR = 'KORFU'                                       >    1
              2  <                                                              >    2
              3  <                                                              >    3

Note
You cannot define selection criteria for fields contained in the lookup file.

To Run the Transaction

1. Issue the `RUN` command.

   Because the fields and the field headers you have chosen for display are too wide to fit on the standard page, the Report Line Length window appears.

2. Mark the option Enlarge Available Line Size.

        13:23                      ***** Super Natural *****                 1998-05-24
        SAMPLE7                       - Selection Editor -                      SNZUL-S

                                 +-----------------Report Line Length-----------------+
              1  < START-HARBOR  !                                                    !
              2  <               ! The selected fields required a line size of .. 121 !
              3  <               ! The field headers required a line size of .... 122 !
              4  <               ! The available Online  line size at present ... 79  !
              5  <               !                                                    !
              6  <               !   x  Enlarge Available Line Size                   !
              7  <               !   _  Change to Report Type Vertical List           !
              8  <               !   _  Truncate Headers                              !
              9  <               !   _  Remove Excess Fields                          !
             10  <               !   _  Remove Excess Fields and Truncate Headers     !
             11  <               !   _  Modify Transaction                            !
             12  <               !                                                    !
             13  <               +----------------------------------------------------+

3. Press Enter.

   The report is generated. Part of the resulting report is shown below:

        13:24:51  START-HARBOR = 'KORFU'                                     1998-05-24
        ISAMPLE7                                                               1

        START-DATE     START-HARBOR      DESTINATION-HARBOR            YACHT-TYPE
        ---------- -------------------- -------------------- --------------------------

          19910420 KORFU                KEFALONIA            MIKADO 56
          19910518 KORFU                KORFU                MIKADO 56
          19910518 KORFU                REGGIO DI CALABRIA   15-M-STEILGAFFELSCHONER
          19910525 KORFU                KORFU                MIKADO 56
          19910601 KORFU                KEFALONIA            MIKADO 56
          19910629 KORFU                KORFU                FIRST 38 S
          19910713 KORFU                ZAKYNTHOS            FIRST 38 S
          19911019 KORFU                KALAMATA             SKORPION III

        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
                    Print Exit              Flip        +     Posi  <     >     Canc

4. Use PF11 and PF10 to shift to the left and right in the report.

5. Use PF9 to position the field at cursor position to the left-hand side of the screen.