SQL

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

SQL

Paths and Views

A Path is a logical connection between two records or tables which tells SQL how to join the two data sources when they are referenced in a FROM clause on a SELECT.

A View is a virtual table which is created from one or more records or tables and can then be referenced in the FROMclause as if it was a real table.

Create paths and views with the CREATE PATH and CREATE VIEW commands. List paths and views with the SHOW command. Delete paths and views with the DROP command.

Paths

Paths are implicitly invoked if the path is the only path between two records or tables named in the FROM clause of a SELECT. If there is more than one path defined between two records or tables, SQL uses the earliest defined path. When a SIR/XS database is connected, paths are automatically created between all records with matching keys.

Use the SHOW PATHS command to see the currently defined paths in the order that SQL deals with them.

Paths are explicitly invoked in the FROM clause of the SELECT statement by specifying the name of the first record type or table and the path name as the second name. For example, if there is a PATH called NEWPATH from RECX to RECY, invoke the path with:

SELECT RECXVARS RECYVARS FROM RECX NEWPATH

A path defined between two records on a case structured database operates within the same case unless the USING clause is specified on the path. The USING clause joins records belonging to different cases. The setting of CASE mode does not affect the operation of the path. Paths can be defined between the common information record (CIR) and other records or CIRs by specifying the keyword CIR.

A table name may be specified and the rows on the table are used in exactly the same way as records on the database. The WHERE clause in the path definition determines how the path operates and should normally reference indexed columns. VIA and USING clauses are not used if the TO clause specifies a table.

View

A View is a virtual table which does not physically exist. It is a mapping that retrieves data from tables and records and presents it as if it was a table. A view can provide a simpler presentation of a given subset of data. The view is dynamic and reflects the latest data contained in the base records and tables. Views provide additional security; permissions for various activities on the view may be granted to specific users; security on columns and rows may be controlled through the SELECT statement and the WHERE clause within the view.

A view definition resides in a tabfile and its columns are defined by a SELECT statement on the CREATE VIEW command. This is the same as any other SELECT except that the DISTINCT, ORDER BY, FORMAT and UNION clauses may not be used.

When a view is created, it is created on the tabfile in the same way as any other table. Views can be exported and imported on another machine. Security can be defined as for any other table. Permissions on views can be granted and revoked. (See Permissions).

Views can be used to SELECT ... FROM ... as any other table. DISPLAY does not access views. Views are used in SQL only; they cannot be used in other SIR/XS products such as VisualPQL or FORMS.

The underlying data in tables or records can be updated using the VIEW name on the update command with the following conditions:

If the FROM clause in the SELECT statement of the CREATE VIEW command references more than one table or record, the view cannot be updated.

If the SELECT statement implies any aggregation (GROUP BY or aggregation functions), the view cannot be updated.

If the base table is a record or a table with a unique index, the view must include all the columns that compose the key or index in order to be updateable.

If the SELECT statement contains a constant or an expression, then:

INSERT is not allowed on that column
UPDATE is not allowed on that column
DELETE is allowed

If the view does not include all columns or variables from the table or record, then any unspecified columns in new rows or records are assigned undefined values.

CREATE PATH

CREATE PATH path_name
         FROM              [[database.]recordtype] | [[tabfile.] table]
         TO                [[database.]recordtype] | [[tabfile.] table]
         [WHERE boolean]
         [VIA    value list  |  USING   value list ]
         FIRST LAST OUTER REVERSE CIR

CREATE PATH names the path and specifies the two records or tables that the path joins. Paths may use Views as the FROM and TO references. The

WHERE,
VIA,

and USING clauses link records and rows depending on the values of data items.

FROM

TO

FROM

WHERE

The

WHERE

VIA

TO

FROM

VIA

USING

TO

USING

FROM

USING

FIRST

TO

LAST

TO

OUTER

TO

REVERSE

REV

Examples:
1) To join employee records to occup records only where the employee is female and works in division 1, specify:

CREATE PATH MYPATH  -
         FROM EMPLOYEE TO OCCUP -
         WHERE GENDER EQ 2 AND DIVISION EQ 1

2) To create a path called CURRENT that joins EMPLOYEE and OCCUP records only for the current position, specify

CREATE PATH CURRENT -
          FROM EMPLOYEE TO OCCUP VIA CURRPOS

Note that VIA and USING are not specified together. Valid Specifications on the VIA, VIA *, and USING clauses are:

FROM     TO      VIA    VIA *    USING
-------  ------- ---  ----   -----
table    recname Yes    No       Yes
table    table   No     No       No
recname  recname Yes    Yes      Yes
recname  table   No     No       No

CREATE VIEW

CREATE VIEW [tabfile.] viewname [(column list) ]
         AS SELECT     variable list ...
         FROM          [database.] rectype | [tabfile.]table , ...
         [GROUP BY     variable list, ... [HAVING expression]]
         [OUTER        [database.] rectype | [tabfile.]table ]
         [WHERE        expression]
         [WITH CHECK OPTION ]

CREATE VIEW creates a named view. The view is a SELECT from a number of records, tables or other views with particular conditions.

viewname

Every view has a name and its definition is stored in a tabfile. If the tabfile is not specified, the current default tabfile is used. column list

The optional column name list renames the columns specified in the SELECT statement in the CREATE VIEW command. The column list must reference the same number of columns as the SELECT statement does. Renaming columns is useful when the SELECT creates columns with awkward names such as expressions or concatenations of table and column names.

AS SELECT

FROM

SELECT

FROM

GROUP BY

Specifies that sets of values selected are grouped together according to their unique values in the value list. If this is a case structured database and the

GROUP BY

CLEAR CASE

HAVING

GROUP BY

OUTER

WHERE

WITH CHECK OPTION

WHERE

Examples:
To create a view of EMPLOYEES with ID, NAME and SALARY for male employees only (including ID automatically because it is the case id):

CREATE VIEW MALES -
      AS SELECT NAME SALARY -
      FROM 1 WHERE GENDER EQ 1

To create a view of EMPLOYEES who have had low review ratings:

CREATE VIEW LOWRATING  -
         AS SELECT NAME SALARY POSITION DIVISION REVDATE RATING -
         FROM 1 2 3  -
         WHERE RATING LT 4

To create a dynamic summary of people by education level:

CREATE VIEW EDSUMM    -
         (LEVEL,NUMBER,WAGES,AVERAGE) -
         AS SELECT EDUC COUNT(SALARY) SUM(SALARY) -
         SUM(SALARY)/COUNT(SALARY) -
         FROM 1  -
         GROUP BY EDUC

RENAME VIEW COMMAND

RENAME VIEW  [ tabfile_name.  ] view_name TO view_name

Renames a view. If the tabfile is not specified, the default tabfile is used.