SQL File Types

Predict offers various file types for documenting tables and views of the SQL systems listed below. The file objects which document the SQL tables and views can be used to generate SQL CREATE statements, DDMs and copy code members for 3GLs. The CREATE statements are stored as Natural members in file FDIC.

This document covers the following topics:


Naming Conventions for SQL Objects

Special naming conventions apply to the following objects in Predict

  • SQL file types. See table below.

  • Fields linked as children to these file types

  • Constraint names

  • Correlation names

  • Tablespace for Oracle

  • The file IDs must be fully qualified. A fully qualified ID consists of three parts:

    • Hyphen to separate creator/schema from table/view name

    • Table/view name. The maximum length depends on the SQL system. See table below.

  • Fully qualified IDs may not exceed 32 characters.

  • The permitted characters listed in the table below apply to creator/schema and table/view name.

  Filetype
  AT,B, A(SQL) BT, BV D, E, IV, IT JT, JV OT, OV X XT, XV YT, YV
Convention Maximum length of table/view name 32 18 18 24 30 18 18 30
Upper case     Y   Y Y    
Upper/lower case Y Y   Y     Y Y
'_' allowed at first pos.     Y Y       Y
'#' allowed at first pos.   Y Y          
'$' allowed at first pos.   Y Y          
'@' allowed at first pos.   Y Y          
'_' allowed from second pos. Y Y Y Y Y Y Y Y
'#' allowed from second pos.   Y Y Y Y Y   Y
'$' allowed from second pos.   Y Y Y Y Y   Y
'@' allowed from sec. pos.   Y Y Y       Y
Numbers allowed from second pos. Y Y Y Y Y Y Y Y

Type-specific rules are also given in the respective parts of this section.

Common Parameters for SQL File Types

The following parameters are valid for all or most SQL file types.

SQL Attributes

These parameters apply to all SQL views.

Select
A Select all: Redundant duplicates are not eliminated.
D Select distinct: Redundant duplicates are eliminated.
With check option
Y All inserts and updates to the view are checked against the view definition.

Additional attributes / Associations

Profile options are described in the section Defaults in the Predict Administration documentation. The editors are described in the section Editors in Predict in the Predict Reference documentation.

Subquery

This option is available for all SQL views.

Enter Y in the Subquery field to call an Editor to edit the subquery clause of the SQL view. The editor called depends on the preferences specified in the Profile > Handling screen:

  • if your first choice editor is Natural, the Subquery Editor (a modified Natural Editor) is called.

  • if your first choice editor is SAG or Word for Windows, the Software AG Editor is called.

Additional commands are available for processing subqueries and checks are performed when the subquery is cataloged.

See the section Editors in Predict in the Predict Reference documentation .

Check Expression

This option is available for the following SQL tables:

  • Adabas D

  • DB2

  • Oracle

  • Informix

  • Ingres

  • Sybase

It is also available for the following file type:

  • General SQL file

Enter Y in the Check expression field to edit the check expression of the file. The editor called depends on the preferences specified in the Profile > Handling screen:

  • if your first choice editor is Natural, the Description Editor (a modified Natural Editor) is called.

  • if your first choice editor is SAG or Word for Windows, the Software AG Editor is called.

No special checks are performed when check expression is saved.

Field Lists of SQL Views

The following screen shows the layout of the field list of an SQL file.

    >                     > + Fi: ARH-E1                           L:    1 S: 5
       Ty L Field ID           from Table/View ID          Field ID           All
       *- - ------------------ --------------------------- ------------------
          1 ARH1               ARH-D1                      ARH1
       SP 1 ARH_SP             ARH-D1                      ARH_SP
          1 ARH4               ARH-D1                      ARH4
          1 TIME_1             PD-E1                       TIME_1
   

Column Meaning
Ty Field type.
L Field level.
Field ID ID of field object documenting the SQL view. The ID of the field object in Predict documenting a field in a view can differ from the name of the field in the original table or view.
from Table/View ID ID of the Predict file documenting the table or view from which the field was taken. If this file contains a subquery clause with a correlation name for the table or view, the correlation name must be entered instead of the file ID.
from field ID Field in the table or view from which it was taken.

Adding new Fields to Field Lists of SQL Views

New fields can easily be inserted into the field list of an SQL view using one the following two methods:

Manually

Enter parameters Field ID, from Table/View ID and from Field ID described above. See the section Naming Conventions for SQL Objects.

With Command SELECTImport

Use the command SELECT to select fields from other SQL tables or views and insert them into the current field list. The following screen appears:

    13:06:46             *****  P R E D I C T  *****             2007-05-31
    Plan   2                  - Field Selection Menu -                 Profile HNO
   
    File ID ............ HNO-XV
                                                         Added 2007-05-31 at 13:05
                                                            by HNO
    Select object type .............. EL  ( Field )
   
    Retrieval type ......* D
    Output mode .........* S Select
   
    Search criteria
      Field ID/Synonym ...                                  Synonym of language*
      Belongs to FI ......                                  Files of type .....*
   
   
    Restrictions ........*   Profile HNO,used               Association .......*

Note:
Parameters not listed here are described in the section Retrieval in the Predict Reference documentation .

Parameters
Retrieval type
The following field retrieval functions are available to select fields for insertion into the field list:
D Fields
N Non-standard fields
U Fields with no verification.
Search criteria
Field ID/Synonym This parameter corresponds to from Field ID in the field list of SQL views shown above.
Belongs to FI This parameter corresponds to from Table/View ID in the field list of SQL views shown above.
Files of type It is possible to select fields of any type for insertion. However, an error message will be given when you try and catalog a field list containing fields with an incompatible type. See list of compatible field types in the section Structure of a Subquery Clause. If a unique field ID is specified, this parameter is ignored.
Mark the fields to be inserted with X, S or /. Selected fields are marked *ins* in the field list. Catalog the list to add the fields to the list.

Editing the Subquery of an SQL View

Calling the Editor

Two methods are available for calling an editor to edit the subquery of an SQL view:

  1. enter Y in the Subquery field in the Additional attributes window of the Add File, Copy File or Modify File screen, or

  2. enter the command EDIT FILE SUBQUERY <File ID>

The editor called depends on the preferences specified in the Profile > Handling screen:

  • if your first choice editor is Natural, the Subquery Editor (a modified Natural Editor) is called.

  • if your first choice editor is SAG or Word for Windows, the Software AG Editor is called.

   >                     > + VI: PD-E1                            L: 1    S: 8
     All  ....+....1....+....2..Subquery clause .4....+....5....+....6....+....7..
          FROM
          PD-D1 D1 ,
          SMR-D
          WHERE
            'ABC' IN
            ( SELECT A-COL2 FROM PD-D1 A)
   
   

Structure of a Subquery Clause

The following rules apply:

  • In the first part of the subquery clause, the related master files and their correlation names can be specified in SQL syntax.

  • The file type of the related master files must be compatible with the file type:

    File Type of View Related Master File Type
    B A(SQL), AT, B
    BV BT, BV
    E, IV D, E, IV
    JV JT, JV
    OV OT, OV
    XV XT, XV
    YV YT, YV
  • Any correlation name that is specified must be used whenever the file is referred to. Type-dependent rules apply to the length of a correlation name and the characters permitted. See table in the section Naming Conventions for SQL Objects.

  • The first part of the subquery is generated automatically if the fields of the file are defined in Predict before the subquery is edited.

  • The second part of the subquery contains the selection criteria of the view: the WHERE clause, GROUP BY clause or HAVING clause or any combination of these. The name of each field referenced in the selection criteria must be qualified by the ID of the file from which the field is taken or - if a correlation name has been specified in the first part of the subquery - by the correlation name.

  • If joined views are edited, the selection criteria and the type of join are displayed for each join. See example below.

      >                     > + VI: LE-SEIGANZ                       L:    1 S:    8 
      All  ....+....1....+....2..Subquery clause .4....+....5....+....6....+....7.. 
            FROM                                                                    
           BI-UTEREST U                                                             
            LEFT OUTER JOIN                                                         
           BI-LANGUET L                                                             
            ON U-CLANGUE = L-CLANGUE                                                
            LEFT OUTER JOIN                                                         
           BI-ZONRSPT Z                                                             
            ON U-CRESEAU = Z-CRESEAU AND U-NZONRSP = Z-NZONRSP
    
  • If union views are edited, placeholders are used instead of field selection lists, because field selection lists are maintained with the field list editor. The placeholder indicates where the selected field list will be added when generating the view. Placeholders are displayed in angle brackets. See example below.

     >                     > + VI: LE-UTERESV1                      L:    1 S:   15 
      All  ....+....1....+....2..Subquery clause .4....+....5....+....6....+....7..                                                                    
            <SELECTION LIST 1 >                                                     
            FROM                                                                    
           BI-UTEREST U                                                             
           WHERE U-CLANGUE LIKE 'A%'                                                
            UNION                                                                   
            <SELECTION LIST 2 >                                                     
            FROM                                                                    
           BI-ZONRSPT Z                                                             
           WHERE Z-CRESEAU LIKE 'B%'                                                
            UNION                                                                   
            <SELECTION LIST 3 >                                                     
            FROM                                                                    
           BI-DISTRIT D                                                             
           WHERE D-NDISTRI LIKE 'D%' 
    
  • When generating a CREATE VIEW statement for a view, hyphens (-) are replaced by underscores (_) or points (.).

  • The subquery can include comment lines (with /*, * or ** in the first two columns) and line comments (preceded by /*).