Example: Using the list selection function

 

This example shows how to use the list selection function to build a SELECT statement.

Assume you have:

• Just entered interactive SQL by typing STRSQL on an i5/OS^® command line.

• Made no list selections or entries.

• Selected *SQL for the naming convention.

The example shows lists that are not on your server. They are used as an example only.

Begin using SQL statements:

1. Type SELECT on the first statement entry line.

2. Type FROM on the second statement entry line.

3. Leave the cursor positioned after FROM.

                                Enter SQL Statements  
   Type SQL statement, press Enter.
   ===> SELECT      FROM _
    
    
    
    
    

4. Press F17=Select tables to obtain a list of tables, because you want the table name to follow FROM.

   Instead of a list of tables appearing as you expected, a list of collections appears (the Select and Sequence collections display). You have just entered the SQL session and have not selected a schema to work with.

5. Type a 1 in the Seq column next to YOURCOLL2 schema.

                          Select and Sequence Collections  
    
   Type sequence numbers (1-999) to select collection, press Enter.
    
   Seq   Collection      Type   Text       YOURCOLL1       SYS    Company benefits 1     YOURCOLL2       SYS    Employee personal data       YOURCOLL3       SYS    Job classifications/requirements       YOURCOLL4       SYS    Company insurances  
    

6. Press Enter.

   The Select and Sequence Tables display appears, showing the tables existing in the YOURCOLL2 schema.

7. Type a 1 in the Seq column next to PEOPLE table.

                           Select and Sequence Tables  
   Type sequence numbers (1-999) to select tables, press Enter.
    
   Seq  Table                    Collection    Type  Text      EMPLCO                   YOURCOLL2     TAB   Employee company data 1    PEOPLE                   YOURCOLL2     TAB   Employee personal data      EMPLEXP                  YOURCOLL2     TAB   Employee experience      EMPLEVL                  YOURCOLL2     TAB   Employee evaluation reports      EMPLBEN                  YOURCOLL2     TAB   Employee benefits record      EMPLMED                  YOURCOLL2     TAB   Employee medical record      EMPLINVST                YOURCOLL2     TAB   Employee investments record  

8. Press Enter.

   The Enter SQL Statements display appears again with the table name, YOURCOLL2.PEOPLE, inserted after FROM. The table name is qualified by the schema name in the *SQL naming convention.

                                Enter SQL Statements  
   Type SQL statement, press Enter.
   ===> SELECT      FROM YOURCOLL2.PEOPLE _
    
    
    
    
    

9. Position the cursor after SELECT.

10. Press F18=Select columns to obtain a list of columns, because you want the column name to follow SELECT.

    The Select and Sequence Columns display appears, showing the columns in the PEOPLE table.

11. Type a 2 in the Seq column next to the NAME column.

12. Type a 1 in the Seq column next to the SOCSEC column.

                            Select and Sequence Columns  
    Type sequence numbers (1-999) to select columns, press Enter.
     
    Seq  Column                Table                   Type         Digits  Length 2    NAME                  PEOPLE                  CHARACTER                 6
         EMPLNO                PEOPLE                  CHARACTER                30
    1    SOCSEC                PEOPLE                  CHARACTER                11
         STRADDR               PEOPLE                  CHARACTER                30
         CITY                  PEOPLE                  CHARACTER                20
         ZIP                   PEOPLE                  CHARACTER                 9
         PHONE                 PEOPLE                  CHARACTER                20
     

13. Press Enter.

    The Enter SQL Statements display appears again with SOCSEC, NAME appearing after SELECT.

                                 Enter SQL Statements  
    Type SQL statement, press Enter.
    ===> SELECT SOCSEC, NAME      FROM YOURCOLL2.PEOPLE  
     
     
     
     

14. Press Enter.

    The statement you created is now run.

Once you have used the list function, the values you selected remain in effect until you change them or until you change the list of schemas on the Change Session Attributes display.

 

Parent topic:

Using the list selection function