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 | |||||||||||
[WITH_CTE
common-table-expression, ]
|
||||||||||||
SELECT
selection
into-clause
table-expression
|
||||||||||||
|
||||||||||||
[ORDER BY
criteria] |
||||||||||||
OPTIMIZE
FOR
integer |
|
|||||||||||
[WITH
isolation-level] |
||||||||||||
[SKIP LOCKED
DATA] |
||||||||||||
[QUERYNO
integer]
|
||||||||||||
[OFFSET
row-count] |
||||||||||||
[FETCH FIRST
row-limit] |
||||||||||||
[WITH HOLD]
|
||||||||||||
[WITH RETURN]
|
||||||||||||
[WITH
scroll-mode] |
||||||||||||
[WITH
ROWSET POSITIONING FOR
max-rowsets] |
||||||||||||
[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.
OFFSET
|
[offset-row-count] |
|
The OFFSET
clause specifies the number of rows to skip in the result table
before retrieving any rows from there. A limited number of rows at the end of a result
set can improve the performance of queries with potentially large result sets.
offset-row-count is a numeric variable or constant which
determines the number of rows to be skipped. The number must be zero (0
) or
a positive integer.
FETCH FIRST
|
|
|
ONLY
|
The FETCH FIRST
clause limits the number of rows to be fetched. It
improves the performance of queries with potentially large result sets if only a limited
number of rows is needed.
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 |
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
|
sort-key | ASC |
, | ||||||
DESC |
|||||||||
INPUT SEQUENCE |
|||||||||
ORDER
OF
table-designator |
The ORDER BY
clause arranges the result set of a SELECT
statement in a particular sequence.
The result set can be ordered by sort-key, by INPUT SEQUENCE
or BY
ORDER OF table-designator
.
Syntax Element Description:
Syntax Element | Description |
---|---|
sort-key |
You have the following options to specify a sort key:
The expression may consist of columns of the result set host variables and constants. If multiple sort keys exist, the rows are ordered by the first sort key; duplicate first sort keys are ordered by the second sort key, and so on. If a column name is specified in the sort key of a fullselect including a
set operator ( |
ASC|DESC |
Specifies the sort order for the sort key: ascending
(ASC ) or descending (DESC ). ASC is
the default. See Example
2.
|
INPUT-SEQUENCE |
Indicates the result table reflects the input order of the
rows specified in the VALUES clause of an INSERT statement.
|
ORDER OF
table-designator |
Specifies the result table rows should be ordered in the
same way as the table designated by the
table-designator .
|
- 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 ...
QUERYNO
integer
|
The QUERYNO
clause specifies the number to be used for this SQL
statement in EXPLAIN
output and trace records. The number is used as
QUERYNO
column in the PLAN_TABLE
for the rows that contain
information on this statement.
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.
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_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:
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
|
The WITH HOLD
clause is used to prevent cursors from being closed by a
commit operation within database loops. If WITH HOLD
is specified, a
commit operation commits all the modifications of the current logical unit of work,
but releases only locks that are not required to maintain the cursor. This optional
clause is mainly useful in batch mode; it is ignored in CICS pseudo-conversational
mode and in IMS message-driven programs.
- Example
SELECT name INTO #name FROM table WHERE AGE = 2 WITH HOLD
WITH
|
This clause allows you to specify an explicit isolation level with which the statement is to be executed.
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 RETURN
|
The WITH RETURN
clause is used to create result sets. Therefore, this
clause only applies to programs which operate as Natural stored procedure. If the
WITH RETURN
clause is specified in a SELECT
statement, the
underlying cursor remains open when the associated processing loop is left, except
when the processing loop had read all rows of the result set itself. During first
execution of the processing loop, only the cursor is opened. The first row is not yet
fetched. This allows the Natural program to return a full result set to the caller of
the stored procedure. It is up to you to decide how many rows are processed by the
Natural stored procedure and how many unprocessed rows of the result set are returned
to the caller of the stored procedure. If you want to process rows of the select
operation in the Natural stored procedure, you must define
IF *counter =1 ESCAPE TOP END-IF
in order to avoid processing of the first "empty row" in the processing loop. If you decide to terminate the processing of rows, you must define the following statement in the processing loop:
IF condition ESCAPE BOTTOM END-IF
If the program reads all rows of the result set, the cursor is closed and no result
set is returned for this SELECT WITH RETURN
to the caller of the stored
procedure.
The following programs are examples for retrieving full result sets (Example 1) and partial result sets (Example 2).
- Examples
DEFINE DATA LOCAL . . . END DEFINE * * Return all rows of the result set * SELECT * INTO VIEW V2 FROM SYSIBM-SYSROUTINES WHERE RESULT_SETS > 0 WITH RETURN ESCAPE BOTTOM END-SELECT ENDDEFINE DATA LOCAL . . . END DEFINE * * Read the first two rows and return the rest as result set * SELECT * INTO VIEW V2 FROM SYSIBM-SYSROUTINES WHERE RESULT_SETS > 0 WITH RETURN WRITE PROCEDURE *COUNTER IF *COUNTER = 1 ESCAPE TOP END-IF IF *COUNTER = 3 ESCAPE BOTTOM END-IF END-SELECT END
WITH
|
|
[:]
scroll_hv [GIVING
[:] sqlcode]
|
Natural for Db2 supports Db2 scrollable cursors by using the clauses WITH
ASENSITIVE SCROLL
, WITH SENSITIVE STATIC SCROLL
, and
SENSITIVE DYNAMIC SCROLL
. Scrollable cursors allow Natural for Db2 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.
Db2 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.
The positioning is performed depending on the content of the
scroll_hv
. The content is evaluated each
time a FETCH
against Db2 is executed.
Syntax Element Description:
Syntax Element | Description |
---|---|
ASENSITIVE SCROLL |
Specifies that the cursor is either INSENSITIVE
or SENSITIVE DYNAMIC .
This is determined by Db2 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. 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. 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. Additionally, it specifies the sensitivity ofUPDATE
s orDELETE
s against the base table row during aFETCH
operation. The content ofscroll_hv
is evaluated each time the database processing loop cycle is executed.
INSENSITIVE
SENSITVE
AFTER
BEFORE
CURRENT
FIRST
LAST
PRIOR
NEXT
ABSOLUTE
RELATIVE
+
-
integer - scroll_hv Sensitivity Specification
The specification of the sensitivity
INSENSITIVE
orSENSITIVE
is optional.
If it is omitted from a
FETCH
against anINSENSITIVE SCROLL
cursor, the default will beINSENSITIVE
.If it is omitted from a
FETCH
against aSENSITIVE STATIC
/DYNAMIC SCROLL
cursor, the default will beSENSITIVE
.The sensitivity specifies whether or not the rows in the base table are checked when performing a
FETCH
operation for a scrollable cursor.
If the corresponding base table column qualifies for the
WHERE
clause and has not been deleted, aSENSITIVE FETCH
will return the row of the base table.If the corresponding base table column does not qualify for the
WHERE
clause or has not been deleted, aSENSITIVE FETCH
will return anUPDATE
hole or aDELETE
hole state (SQLCODE +222
).An
INSENSITIVE FETCH
will not check the corresponding base table column.- scroll_hv Options
Below is an explanation of the options available to determine the row(s) to fetch, the position from where to start the fetch and/or the direction in which to scroll:
Option Explanation AFTER
Positions after the last row. No row is fetched. BEFORE
Positions before the first. No row is fetched. 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.See the Db2 SQL reference by IBM about further details regarding positive and negative position numbers.
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.See the Db2 SQL reference by IBM about further details regarding positive and negative position numbers.
- 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. +222
UPDATE
orDELETE
hole, cursor still open, no data returned. The corresponding row of the base table has been updated or deleted, so that the row no longer qualifies for theWHERE
clause.+231
Fetch operation with the option CURRENT
, but cursor not positioned on any row, no data returned. This occurs if the previousFETCH
returnedSQLCODE +100
.If 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
.
WITH ROWSET POSITIONING
FOR |
|
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.
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 ...