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

• Common Parameters for SQL File Types

• Field Lists of SQL Views

• Editing the Subquery of an SQL View

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.

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

• Additional attributes / Associations

• Subquery

• Check Expression

SQL Attributes

These parameters apply to all SQL views.

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
   

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

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, IV
  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 /*).