RELATE Statement

PURPOSE: When you access an Oracle table, you must declare the table in the Control Section of the Process Module as is done for any other Data Set. See the comprehensive example in Appendix B of this manual or Volume II of the ACCENT R Professional Desk Reference Set for more information on the structure of Process Modules (PM).

Syntax

RELATE DS table_name {AS, TO, WITH} designator FOR
{GET, PUT, DELETE, /CREATE/ APPEND, UPDATE}

table_name	is the name of the Data Set object in the ACCENT R DBL and the name of the Oracle table.
AS, TO, WITH	are synonymous; any one of the three can be used.
designator	is the name by which the Data Set will be referenced in statements in the Process Module. The designator may be the Data Set name but it must still be specified in a RELATE statement. The designator name must follow the Oracle naming convention (see NOTES below). In Oracle terminology, the designator is called the alias table name.
FOR option	controls the Open SQL Process Module statements that you can execute. The options and the statements that you can execute for each are shown in Table 8.

Option	Statements Allowed
GET	SQL SELECT
PUT	SQL SELECT SQL UPDATE
DELETE	SQL SELECT SQL DELETE
/CREATE/ APPEND	SQL SELECT SQL INSERT
UPDATE	SQL SELECT SQL UPDATE SQL DELETE SQL INSERT

Figure 20 FOR Options

ACCENT R users may note that the CREATE option, which clears existing records before adding new ones, is not available. You can accomplish this by executing the SQL DELETE statement described later in this document.

Example

pm title_code_list
! PM TITLE_CODE_LIST
CONTROL SECTION
   RELATE DS BOOKS_ORA AS T1 FOR UPDATE
DETAIL SECTION
   SQL SELECT T1.TITLE_CODE FROM T1
   START:10
     SQL FETCH NEXT ROW
   LEAVE:10 IF @SQLCODE # 0
     TYPE TITLE_CODE:T1
   REPEAT:10

The above example relates the table BOOKS_ORA for UPDATE. The table has a designator name of T1. The designator is used throughout the PM to refer to the table BOOKS_ORA.

Notes: The designator can be the same as the table name or it can be a shorter more convenient name for the table.

Periods are used in Open SQL Process Module statements to separate the qualifiers that uniquely identify a table or column. In ACCENT R terminology, a qualifier is an auxiliary name or a designator name.

You must follow the SQL standard for column names used to refer to Oracle data in all of the SQL Process Module statements (such as SQL SELECT, SQL INSERT, SQL UPDATE, or SQL DELETE). All Oracle column names used in the SQL Process Module statements must have this form:

designator.column_name (if the name is not unique)

or

column_name (if the name is unique)

The form:

column_name:designator

is not allowed in the SQL Process Module statements. However, when you refer to a Oracle column outside of an SQL statement, you use the “column_name:designator” form.

For example, in an ACCENT R Process Module statement, the name:

TITLE_CODE:T1

means the field TITLE_CODE in the Data Set that was related with the designator T1. However, in an SQL Process Module statement, you must comply with the SQL standard and you must use:

T1.TITLE_CODE

instead of TITLE_CODE:T1

The following two Process Module statements give another example:

SQL SELECT BOOKAUTHOR_ORA.AUTHOR_CODE FROM BOOKAUTHOR_ORA, AUTHOR_ORA

:

:

TYPE AUTHOR_CODE:BOOKAUTHOR_ORA

In general, it is recommended that you do not use the dot (".") in applications making use of Open SQL because of the potential for confusion in syntax.