This document covers the following topics:
For explanations of the symbols used in the syntax diagrams, see Syntax Symbols.
Belongs to Function Group: Database Access and Update
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.
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.
SELECT selection
into-clause
table-expression
|
|||||||||||
|
|||||||||||
[ORDER BY
criteria] |
|||||||||||
statement | |||||||||||
SELECT selection
into-clause
table-expression
|
||||||||||||
|
||||||||||||
[ORDER BY
criteria] |
||||||||||||
[OPTIMIZE
FOR integer ROWS]
|
||||||||||||
[WITH
isolation-level] |
||||||||||||
[FETCH
FIRST row-limit] |
||||||||||||
[WITH
scroll-mode] |
||||||||||||
[IF NO RECORDS
FOUND instruction] |
||||||||||||
statement | ||||||||||||
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.
SELECT
SINGLE
|
|||||||
selection into-clause table-expression | |||||||
[ IF NO RECORDS
FOUND instruction] |
|||||||
statement | |||||||
SELECT SINGLE
|
|||||||
selection into-clause table-expression | |||||||
[ WITH
isolation-level] |
|||||||
[ FETCH FIRST
row-limit] |
|||||||
[ IF NO RECORDS
FOUND instruction] |
|||||||
statement | |||||||
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:
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
|
|
|
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.
Note:
This clause actually does not belong to Natural SQL; it represents
Natural functionality which has been made available to SQL loop
processing.
IF NO [RECORDS ] [FOUND ]
|
||
ENTER
|
||
statement | ||
END-NOREC
|
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.
Note:
If the result set of the SELECT
statement consists of
a single row of NULL
values, the IF NO RECORDS FOUND
clause is not executed. This could occur if the selection list consists solely
of one of the aggregate functions SUM
, AVG
,
MIN
or MAX
on columns, and the set on which these
aggregate functions operate is empty. When you use these aggregate functions in
the above-mentioned way, you should therefore check the values of the
corresponding null-indicator fields instead of using an IF NO RECORDS
FOUND
clause.
- 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
|
parameter, |
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).
Note:
In standard SQL syntax, an INTO
clause is only used in
non-cursor select operations (singleton SELECT
) and can be
specified only if a single row is to be selected. In Natural, however, the
INTO
clause is used for both cursor-oriented and non-cursor select
operations.
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 Note: |
correlation-name
|
If the |
- Examples
Example 1:
DEFINE DATA LOCAL 01 PERS VIEW OF SQL-PERSONNEL 02 NAME 02 AGE END-DEFINE ... SELECT * INTO NAME, AGEExample 2:
... SELECT * INTO VIEW PERSThese examples are equivalent to the following ones:
Example 3:
... SELECT NAME, AGE INTO NAME, AGEExample 4:
... SELECT NAME, AGE INTO VIEW PERSDEFINE 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
andAGE
, which are part of a Natural view, receive the contents of the table columnsFIRSTNAME
andAGE
.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 |
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 |
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 ...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 ...
See Selection in Select Expressions.
The Natural statement(s) to be executed in the processing loop.
See table-expression in Select Expressions.
|
|
||||||||
UNION
, EXCEPT
and INTERSECT
introduce a query that involves set operations.
Set operations combine the results of two or more
select-expressions
.
The columns specified in the individual
select-expressions
must match in
number, type and format.
The INTO
clause must be specified with the first
select-expression
only.
Syntax Element Description:
Syntax Element | Description |
---|---|
UNION |
Combines the results of two or more
select-expressions .
|
EXCEPT |
Specifies the difference set of the result sets
of two select-expressions .
|
INTERSECT |
Specifies the intersection of two result sets. |
DISTINCT |
Specifies that the result set does not contain
redundant (duplicate) rows. DISTINCT is the default
setting.
|
ALL |
Specifies that the result set contains redundant
(duplicate) rows. Redundant duplicate rows are eliminated from the result of a
set operation unless the set operation explicitly includes the ALL
qualifier.
|
- 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
|
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
|
WITH
|
|
[:]
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.
Note:
Not all SQL database systems support all options.
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
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.
|
Note:INSENSITIVE
and
SENSITIVE STATIC
scrollable cursors use temporary result tables
and require a TEMP
database in DB2 (see the relevant DB2
literature by IBM).
- 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 content ofscroll_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
orRELATIVE
.Specifies the position of the row to be fetched
ABSOLUTE
orRELATIVE
.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 supportsNEXT
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 DB2SQLCODE
of the underlyingFETCH
operation. This allows the application to react to different statuses encountered while the scrollable cursor is open. The most important status codes indicated bySQLCODE
are listed in the following table:
SQLCODE Explanation 0
FETCH
operation successful, data returned except forFETCH
with optionBEFORE
orAFTER
.+100
Row not found, cursor still open, no data returned. -1
General error while trying to FETCH
a rowIf you specify
GIVING [:] sqlcode
, the application must react to the different statuses. If anSQLCODE +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 anESCAPE
statement.If you do not specify
GIVING [:] sqlcode
, except forSQLCODE 0
andSQLCODE +100
, eachSQLCODE
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 librarySYSDB2
.
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.
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 ...