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.