This document covers the following topics:

• Function

• Syntax 1 - Cursor-Oriented Selection

• Syntax 2 - Non-Cursor Selection

• Syntax Element Description

• Join Queries

For explanations of the symbols used in the syntax diagrams, see Syntax Symbols.

Belongs to Function Group: Database Access and Update

---

Function

The SELECT statement supports both the cursor-oriented selection that is used to retrieve an arbitrary number of rows and the non-cursor selection (singleton SELECT) that retrieves at most one single row. With the SELECT ... END-SELECT construction, Natural uses the same database loop processing as with the FIND statement.

Two different structures are possible.

Syntax 1 - Cursor-Oriented Selection

Like the Natural FIND statement, the cursor-oriented SELECT statement is used to select a set of rows (records) from one or more DB2 tables, based on a search criterion. Since a database loop is initiated, the loop must be closed by a LOOP (reporting mode) or END-SELECT statement. With this construction, Natural uses the same loop processing as with the FIND statement.

In addition, no cursor management is required from the application program; it is automatically handled by Natural.

• Syntax 1 - Common Set

• Syntax 1 - Extended Set

Syntax 1 - Common Set

SELECT selection into-clause table-expression
		UNION EXCEPT INTERSECT			ALL			(SELECT selection table-expression) SELECT selection table-expression
[ORDER BY criteria]
statement
	END-SELECT LOOP

Syntax 1 - Extended Set

[WITH_CTE common-table-expression, ]

SELECT selection into-clause table-expression

UNION EXCEPT INTERSECT

ALL

(SELECT selection table-expression) SELECT selection table-expression

[ORDER BY criteria]

[OPTIMIZE FOR integer ROWS]

[WITH isolation-level]

[QUERYNO integer]

[FETCH FIRST row-limit]

[WITH HOLD]

[WITH RETURN]

[WITH scroll-mode]

[WITH ROWSET POSITIONING FOR max-rowsets]

[IF NO RECORDS FOUND instruction]

statement

END-SELECT LOOP

Syntax 2 - Non-Cursor Selection

The SELECT SINGLE statement supports the functionality of a non-cursor selection (singleton SELECT); that is, a select expression that retrieves at most one row without using a cursor. It cannot be referenced by a positioned UPDATE or a positioned DELETE statement.

• Syntax 2 - Common Set

• Syntax 2 - Extended Set

Syntax 2 - Common Set

SELECT SINGLE
selection into-clause table-expression
[IF NO RECORDS FOUND instruction]
statement
	END-SELECT LOOP

Syntax 2 - Extended Set

SELECT SINGLE
selection into-clause table-expression
[WITH isolation-level]
[FETCH FIRST row-limit]
[IF NO RECORDS FOUND instruction]
statement
	END-SELECT LOOP

Syntax Element Description

This section alphabetically lists and explains the syntax items contained in the syntax diagrams of Syntax 1 - Cursor-Oriented Selection and Syntax 2 - Non-Cursor Selection:

• END-SELECT | LOOP

• FETCH FIRST row-limit

• IF NO RECORDS FOUND instruction

• into-clause

• OPTIMIZE FOR integer ROWS

• ORDER BY criteria

• QUERYNO Clause

• selection

• SKIP LOCKED DATA

• statement

• table-expression

• UNION Clause

• WITH_CTE common-table-expression

• WITH HOLD Clause

• WITH isolation-level

• WITH RETURN Clause

• WITH scroll-mode

• WITH ROWSET POSITIONING FOR max-rowsets

END-SELECT | LOOP

In structured mode, the Natural reserved keyword END-SELECT must be used to end the SELECT statement.

In reporting mode, the LOOP statement must be used to end the SELECT statement.

FETCH FIRST row-limit

FETCH FIRST

1 integer

ROW ROWS

ONLY

The FETCH FIRST clause limits the number of rows to be fetched. A limited number of rows can improve the performance of queries with potentially large result sets.

This clause is only valid against DB2 databases. When used against other databases, it will cause runtime errors.

IF NO RECORDS FOUND instruction

Structured Mode Syntax

IF NO [RECORDS] [FOUND]
	ENTER
	statement
END-NOREC

Reporting Mode Syntax

IF NO [RECORDS] [FOUND]
	ENTER
	statement
	DO statement DOEND

 The IF NO RECORDS FOUND clause is used to initiate a processing loop if no records meet the selection criteria specified in the preceding SELECT statement.

If no records meet the specified selection criteria, the IF NO RECORDS FOUND clause causes the processing loop to be executed once with an "empty" record. If this is not desired, specify the statement ESCAPE BOTTOM within the IF NO RECORDS FOUND clause.

If one or more statements are specified with the IF NO RECORDS FOUND clause, the statements are executed immediately before the processing loop is entered. If no statements are to be executed before entering the loop, the keyword ENTER must be used.

Database Values

Unless other value assignments are made in the statements accompanying an IF NO RECORDS FOUND clause, Natural resets to empty all database fields which reference the file specified in the current loop.

Evaluation of System Functions

Natural system functions are evaluated once for the empty record that is created for processing as a result of the IF NO RECORDS FOUND clause.

into-clause

INTO

parameter, VIEW {view-name [correlation-name ]},

The INTO keyword introduces an INTO clause. This clause is used to specify the target fields in the program which are to be filled with the result of the selection.

The INTO clause can specify either single parameters or one or more views as defined in the DEFINE DATA statement.

All target field values can come either from a single table or from more than one table as a result of a join operation (see also Join Queries).

The selection can also merely consist of an asterisk (*). In a standard select expression, this is a shorthand for a list of all column names in the table(s) specified in the FROM clause. In the Natural SELECT statement, however, the same syntactical item SELECT * has a different semantic meaning: all the items listed in the INTO clause are also used in the selection. Their names must correspond to names of existing database columns.

Syntax Element Description:

Syntax Element	Description
parameter	If single parameters are specified as target fields, their number and formats must correspond to the number and formats of the columns and/or scalar-expressions specified in the corresponding selection as described above (for details, see Scalar Expressions). See Example 5.
view-name	The name a Natural view as defined in the DEFINE DATA statement. If one or more views are referenced in the INTO clause, the number of items specified in the selection must correspond to the number of fields defined in the view(s) (not counting group fields, redefining fields and indicator fields). Note: Both the Natural target fields and the table columns must be defined in a Natural DDM. Their names, however, can be different, since assignment is made according to their sequence. See Example 5.
correlation-name	If the VIEW clause is used within a SELECT * construction where multiple tables are to be joined, correlation-names are required if the specified view contains fields that reference columns which exist in more than one of these tables. In order to know which column to select, all these columns are qualified by the specified correlation-name at generation of the selection list. The correlation-name assigned to a view must correspond to one of the correlation-names used to qualify the tables to be joined. See also the section Join Queries and Example 6.

Examples

Example 1:

DEFINE DATA LOCAL
01 PERS VIEW OF SQL-PERSONNEL  
  02 NAME
  02 AGE
END-DEFINE
...
SELECT *
  INTO NAME, AGE

Example 2:

...
SELECT *
  INTO VIEW PERS

These examples are equivalent to the following ones:

Example 3:

...
SELECT NAME, AGE  
  INTO NAME, AGE

Example 4:

...
SELECT NAME, AGE  
  INTO VIEW PERS

Example 5:

DEFINE DATA LOCAL
01 PERS VIEW OF SQL-PERSONNEL   
  02 NAME
  02 AGE
END-DEFINE
...
SELECT FIRSTNAME, AGE
  INTO VIEW PERS
  FROM SQL-PERSONNEL
...

The target fields NAME and AGE, which are part of a Natural view, receive the contents of the table columns FIRSTNAME and AGE.

Example 6:

DEFINE DATA LOCAL
01 PERS VIEW OF SQL-PERSONNEL
  02 NAME
  02 FIRST-NAME
  02 AGE
END-DEFINE
...
SELECT *
  INTO VIEW PERS A
  FROM SQL-PERSONNEL A, SQL-PERSONNEL B  
...

OPTIMIZE FOR integer ROWS

OPTIMIZE FOR integer ROWS

This clause is only valid against DB2 databases. When used against other databases, it will cause runtime errors.

The OPTIMIZE FOR integer ROWS clause is used to inform DB2 in advance of the number (integer) of rows to be retrieved from the result table. Without this clause, DB2 assumes that all rows of the result table are to be retrieved and optimizes accordingly.

This optional clause is useful if you know how many rows are likely to be selected, because optimizing for integer rows can improve performance if the number of rows actually selected does not exceed the integer value (which can be in the range from 0 to 2147483647).

Example

SELECT name INTO 
#name FROM table WHERE AGE = 2 OPTIMIZE FOR 100 ROWS

ORDER BY criteria

ORDER BY			column-reference			ASC
			integer			DESC

The ORDER BY clause arranges the result of a SELECT statement in a particular sequence.

Syntax Element Description:

Syntax Element	Description
column-reference	Each ORDER BY clause must specify a column of the result table. In most ORDER BY clauses a column can be identified either by column-reference (that is, by an optionally qualified column name) or by column number. In a query involving UNION, a column must be identified by column number. See also Column Reference.
integer	In a query involving UNION, a column must be identified by column number. The column number is the ordinal left-to-right position of a column within the selection, which means it is an integer value. This feature makes it possible to order a result on the basis of a computed column which does not have a name.
ASC|DESC	Specifies the sort order: ascending (ASC) or descending (DESC). ASC is the default. See Example 2.

Examples

Example 1:

DEFINE DATA LOCAL
1 #NAME          (A20)
1 #YEARS-TO-WORK (I2)
END-DEFINE
...
SELECT NAME , 65 - AGE
  INTO #NAME, #YEARS-TO-WORK   
  FROM SQL-PERSONNEL
  ORDER BY 2
  ...

Example 2:

DEFINE DATA LOCAL
1 PERS VIEW OF SQL-PERSONNEL
1 NAME
1 AGE
1 ADDRESS   (1:6)
END-DEFINE
...
SELECT NAME, AGE, ADDRESS
  INTO VIEW PERS
  FROM SQL-PERSONNEL
  WHERE AGE = 55
  ORDER BY NAME DESC
  ...

QUERYNO Clause

QUERYNO integer

selection

See Selection in Select Expressions.

SKIP LOCKED DATA

The SKIP LOCKED DATA clause specifies that rows are skipped when incompatible locks are held on the row by other transactions.

statement

The Natural statement(s) to be executed in the processing loop.

table-expression

See table-expression in Select Expressions.

UNION Clause

UNION introduces a query that involves set operations.

The INTO clause must be specified with the first select-expression only.

Syntax Element Description:

Syntax Element	Description
UNION	UNION unites the results of two or more select-expressions. The columns specified in the individual select-expressions must be UNION-compatible; that is, matching in number, type and format.
ALL	Specifies that the result set contains redundant (duplicate) rows. Redundant duplicate rows are always eliminated from the result of a UNION unless the UNION operator explicitly includes the ALL qualifier. With UNION, however, there is no explicit DISTINCT option as an alternative to ALL.

Example

DEFINE DATA LOCAL
01 PERS VIEW OF SQL-PERSONNEL   
  02 NAME
  02 AGE
  02 ADDRESS (1:6)
END-DEFINE
...
SELECT NAME, AGE, ADDRESS
  INTO VIEW PERS
  FROM SQL-PERSONNEL
  WHERE AGE > 55
UNION ALL
SELECT NAME, AGE, ADDRESS
  FROM SQL-EMPLOYEES
  WHERE PERSNR < 100
ORDER BY NAME
...
END-SELECT
...

WITH_CTE common-table-expression

WITH_CTE common-table-expression-name [(column-name,…)] AS (fullselect)

This clause allows you to define a result table which can be referenced in any FROM clause of the SELECT statement that follows.

Syntax Element Description:

Syntax Element	Description
WITH_CTE	The Natural specific keyword WITH_CTE corresponds to the SQL keyword WITH. WITH_CTE will be translated into the SQL keyword WITH by the Natural compiler.
common-table-expression-name	Has to be an unqualified SQL identifier and must be different from any other common-table-expression-name specified in the same statement. Multiple common-table-expression can be specified following the single WITH_CTE keyword. Each common-table-expression-name can be specified in the FROM clause of any common-table-expression-name following or in the FROM clause of the SELECT statement following.
column-name	Has to be an unqualified SQL identifier and must be unique within one common-table-expression-name.
AS (fullselect)	The number of column-names must match the number of columns of the fullselect.

A common-table-expression can be used

• in place of a view to avoid creating the view;

• when the same result table needs to be shared in a fullselect;

• when the result needs to be derived using recursion.

Queries using recursion are useful in applications such as bills of materials.

Example

WITH_CTE                                                        
 RPL (PART,SUBPART,QUANTITY) AS                                 
 (SELECT ROOT.PART,ROOT.SUBPART,ROOT.QUANTITY                   
    FROM HGK-PARTLIST ROOT                                      
   WHERE ROOT.PART ='01'                                       
  UNION ALL                                                     
  SELECT CHILD.PART,CHILD.SUBPART,CHILD.QUANTITY                
    FROM  RPL PARENT, HGK-PARTLIST CHILD                        
    WHERE PARENT.SUBPART = CHILD.PART                            
  )                                                             
SELECT DISTINCT PART,SUBPART,QUANTITY                           
  INTO VIEW V1                                                  
  FROM RPL                                                      
  ORDER BY PART,SUBPART,QUANTITY                                
END-SELECT

WITH HOLD Clause

This clause is currently not supported. When used, it will cause a compiler error.

WITH isolation-level

WITH

CS RR RR KEEP UPDATE LOCK RS RS KEEP UPDATE LOCKS UR

This clause allows you to specify an explicit isolation level with which the statement is to be executed.

This clause is only valid against DB2 databases. When used against other databases, it will cause runtime errors.

The following options are provided:

Option	Meaning
CS	Cursor Stability
RR	Repeatable Read
RR KEEP UPDATE LOCKS	Only applies to Syntax 1 - Extended Set and only if a positioned UPDATE or a positioned DELETE statement is processed with the SELECT statement. Repeatable Read and retaining update locks.
RS	Read Stability
RS KEEP UPDATE LOCKS	Only applies to Syntax 1 - Extended Set and only if a positioned UPDATE or a positioned DELETE statement is processed with the SELECT statement. Read Stability and retaining update locks.
UR	Uncommitted Read UR can only be specified within a SELECT statement and when the table is read-only. The default isolation level is determined by the isolation of the package or plan into which the statement is bound. The default isolation level also depends on whether the result table is read-only or not. To find out the default isolation level, refer to the IBM literature.

WITH RETURN Clause

This clause is not currently supported. When used, it will cause a compiler error.

WITH scroll-mode

WITH

ASENSITIVE SCROLL INSENSITIVE SCROLL SENSITIVE STATIC SCROLL SENSITIVE DYNAMIC SCROLL

[:] scroll_hv [GIVING [:] sqlcode]

Natural supports SQL scrollable cursors by using the clauses WITH ASENSITIVE SCROLL, WITH SENSITIVE STATIC SCROLL and SENSITIVE DYNAMIC SCROLL. Scrollable cursors allow Natural applications to position randomly any row in a result set. With non-scrollable cursors, the data can only be read sequentially, from top to bottom.

RDBMS scrollable cursors are enabled with this clause. Scrollable cursors can be ASENSITIVE, INSENSITIVE, SENSITIVE STATIC or SENSITIVE DYNAMIC.

Scrollable cursors allow the application to position any row in the cursor at any time as long as the cursor is open. Scrollable cursors are not supported for Sybase databases at all. Scrollable cursors are not supported for the MS SQL Server DBLIB interface, but only for the MS SQL Server ODBC interface.

The positioning is performed depending on the content of the scroll_hv. The content is evaluated each time a FETCH against the database is executed.

Syntax Element Description:

Syntax Element	Description
ASENSITIVE SCROLL	Specifies that the cursor is either INSENSITIVE or SENSITIVE DYNAMIC. This is determined by the database at open time of the cursor, depending on the read-only property of the cursor: If the cursor is read-only, the cursor will become INSENSITIVE. If the cursor is not read-only, the cursor will become SENSITIVE DYNAMIC. This is supported for DB2 databases.
INSENSITIVE SCROLL	Specifies that the cursor is insensitive for updates, deletes and inserts executed against the base table, after the cursor has been updated.INSENSITIVE SCROLL refers to a cursor that cannot be used in Positioned UPDATE or Positioned DELETE operations. This is supported for Oracle, Adabas D, Informix, MS SQL Server ODBC and DB2 databases. In addition, once opened, an INSENSITIVE SCROLL cursor does not reflect UPDATE, DELETE or INSERT operations against the base table after the cursor was opened. See also Note.
SENSITIVE STATIC SCROLL	Specifies that the cursor is sensitive for updates and deletes against the base table, but not against inserts, after the cursor has been opened.SENSITIVE STATIC SCROLL refers to a cursor that can be used for Positioned UPDATE or Positioned DELETE operations. This is supported for Adabas D, MS SQL Server ODBC and DB2 databases.In addition, a SENSITIVE STATIC SCROLL cursor reflects UPDATE and DELETE operations of base table rows. The cursor does not reflect INSERT operations. See also Note.
SENSITIVE DYNAMIC SCROLL	SENSITIVE DYNAMIC specifies that the cursor is sensitive for updates, deletes and inserts against the base table, after the cursor has been opened. SENSITIVE DYNAMIC scrollable cursors reflect UPDATE and DELETE operations against the base table while the cursor is open. This is supported for Adabas D, MS SQL Server ODBC and DB2 databases.

scroll_hv

The variable scroll_hv must be alphanumeric.

The variable scroll_hv specifies which row of the result table will be fetched during one execution of the database processing loop. The contents of scroll_hv is evaluated each time the database processing loop cycle is executed.

INSENSITIVE SENSITVE

CURRENT FIRST LAST PRIOR NEXT

ABSOLUTE RELATIVE

+ -

integer

scroll_hv Options

Option	Explanation
CURRENT	Fetches the current row (again).
FIRST	Fetches the first row.
LAST	Fetches the last row.
NEXT	Fetches the row after the current one. This is the default value.
PRIOR	Fetch the row before the current one.
+|-integer	Only applies in connection with ABSOLUTE or RELATIVE. Specifies the position of the row to be fetched ABSOLUTE or RELATIVE. Enter a plus (+) or minus (-) sign followed by an integer. The default value is a plus (+).
ABSOLUTE	Only applies in connection with +|-integer. Uses integer as the absolute position within the result set from where the row is fetched.
RELATIVE	Only applies in connection with +|-integer. Uses integer as the relative position to the current position within the result set from where the row is fetched.

There are some restrictions for special RDBMS systems:

• DB2 does not support the keyword CURRENT.

• In a SELECT FOR UPDATE loop DB2 only supports NEXT as scrolling option.

• MS SQL Server (ODBC interface) does not support the keyword CURRENT.

• Adabas D does not support RELATIVE scrolling.

GIVING [:] sqlcode

The specification of GIVING [:] sqlcode is optional. If specified, the Natural variable [:] sqlcode must be of format I4. The values for this variable are returned from the DB2 SQLCODE of the underlying FETCH operation. This allows the application to react to different statuses encountered while the scrollable cursor is open. The most important status codes indicated by SQLCODE are listed in the following table:

SQLCODE	Explanation
0	FETCH operation successful, data returned except for FETCH with option BEFORE or AFTER.
+100	Row not found, cursor still open, no data returned.
-1	General error while trying to FETCH a row

If you specify GIVING [:] sqlcode, the application must react to the different statuses. If an SQLCODE +100 is entered five times successively and without terminal I/O, the Natural for DB2 runtime will issue Natural error NAT3296 in order to avoid application looping. The application can terminate the processing loop by executing an ESCAPE statement.

If you do not specify GIVING [:] sqlcode, except for SQLCODE 0 and SQLCODE +100, each SQLCODE will generate Natural error NAT3700 and the processing loop will be terminated. SQLCODE +100 (row not found) will terminate the processing loop.

See also the example program DEM2SCRL supplied in the Natural system library SYSDB2.

WITH ROWSET POSITIONING FOR max-rowsets

WITH ROWSET POSITIONING FOR

[:] row_hv integer

ROWS

ROWS_RETURNED [:] ret_row

This clause enables DB2 rowset processing, which corresponds to Natural native DML multi-fetch processing. [:] row_hv (I4) or integer determines the maximum number of rows returned from DB2 to Natural. The number determines either the size of the Natural multi-fetch buffer used for standard multiple row processing or the maximum number of rows returned from DB2 into the Natural program for advanced multiple row processing.

ROWS_RETURNED [:] ret_row Clause:

This clause specifies an I4 variable which will receive the number of rows returned by DB2 on behalf of the last executed DB2 fetch operation for advanced multiple row processing.

Join Queries

A join is a query in which data is retrieved from more than one table. All the tables involved must be specified in the FROM clause.

A join always forms the Cartesian product of the tables listed in the FROM clause and later eliminates from this Cartesian product table all the rows that do not satisfy the join condition specified in the WHERE clause.

Correlation names can be used to save writing if table names are rather long. Correlation names must be used when a column specified in the selection list exists in more than one of the tables to be joined in order to know which of the identically named columns to select.

Example

DEFINE DATA LOCAL
1 #NAME     (A20)
1 #MONEY    (I4)
END-DEFINE
...
SELECT NAME, ACCOUNT
  INTO #NAME, #MONEY
  FROM  SQL-PERSONNEL P, SQL-FINANCE F   
  WHERE P.PERSNR = F.PERSNR
    AND F.ACCOUNT > 10000
    ...