This document describes basic syntactical items, which are referenced within the individual SQL statement descriptions.
This document covers the following topics:
The constants used in the syntactical descriptions of the Natural SQL statements are:
constant
|
The item constant
always refers to a Natural constant.
|
integer
|
The item integer always
represents an integer constant.
|
Note:
If the character for decimal point notation (session parameter
DC
) is set to
a comma (,), any specified numeric constant must not be followed directly by a
comma, but must be separated from it by a blank character; otherwise an error
or wrong results occur.
Invalid Syntax: | Valid Syntax: |
---|---|
|
|
The names used in the syntactical descriptions of the Natural SQL statements are:
The item
authorization-identifier
, which is also
called creator name, is used to qualify database tables and views. See also
authorization-identifier
under table-name
below.
The item ddm-name
always refers
to the name of a Natural data definition module (DDM) as created with the
Natural
DDM
Services.
The item view-name
always
refers to the name of a Natural view as defined in the
DEFINE DATA
statement.
The item column-name
always
refers to the name of a physical database column.
The item location-name
always
denotes the location of the table. Specification of location-name is optional
and belongs to the SQL Extended
Set.
The item table-name
in this
section is used to reference both SQL base tables and SQL viewed tables.
Syntax of item table-name
:
[[location-name.]authorization-identifier.]ddm-name |
Syntax Element Description:
Syntax Element | Description |
---|---|
ddm-name |
A Natural data definition module (DDM) must have been created for a table to be used. The name of such a DDM must be the same as the corresponding database table name or view name. |
location-name |
This optional item specifies the location of the table to be accessed. |
authorization-identifier |
There are two ways of specifying the
authorization-identifier of a database
table or view.
One way corresponds to the standard SQL syntax, in which the
Example: DEFINE DATA LOCAL 01 PERS VIEW OF PERSONNEL 02 NAME 02 AGE END-DEFINE SELECT * INTO VIEW PERS FROM SQL.PERSONNEL ... Alternatively, you can define the
Note: Example: DEFINE DATA LOCAL 01 PERS VIEW OF SQL-PERSONNEL 02 NAME 02 AGE END-DEFINE SELECT * INTO VIEW PERS FROM SQL-PERSONNEL ... If the
In addition to being used in Examples: ... DELETE FROM SQL.PERSONNEL WHERE AGE IS NULL ... ... INSERT INTO SQL.PERSONNEL (NAME,AGE) VALUES ('ADKINSON',35) ... ... UPDATE SQL.PERSONNEL SET SALARY = SALARY * 1.1 WHERE AGE > 30 ... |
The item correlation-name
represents an alias name for a table-name
.
It can be used to qualify column names; it also serves to implicitly qualify
fields in a Natural view when used with the
INTO
clause
of the SELECT
statement.
Example:
DEFINE DATA LOCAL 01 PERS-NAME (A20) 01 EMPL-NAME (A20) 01 AGE (I2) END-DEFINE ... SELECT X.NAME , Y.NAME , X.AGE INTO PERS-NAME , EMPL-NAME , AGE FROM SQL-PERSONNEL X , SQL-EMPLOYEES Y WHERE X.AGE = Y.AGE END-SELECT ...
Although in most cases the use of
correlation-names
is not necessary,
they may help to make the statement clearer.
Syntax of item parameter
:
[: ]
host-variable [INDICATOR
[: ]
host-variable] [LINDICATOR
[: ]
host-variable]
|
Syntax Element Description:
Syntax Element | Description |
---|---|
host-variable
|
A host-variable is a
Natural user-defined variable (no system variable) which is referenced in an
SQL statement. It can be either an individual field or defined as part of a
Natural view.
When defined as a receiving field (for example, in the
When defined as a sending field (for example, in the
See also Natural Formats and SQL Data Types. |
[:]
|
Colon:
To comply with SQL standards, a
Example: SELECT NAME INTO :#NAME FROM PERSONNEL WHERE AGE = :VALUE The colon is always required if the variable name is identical to
an SQL reserved word. In a context in which either a
|
INDICATOR
|
INDICATOR Clause:
The When specified with a receiving
Example: DEFINE DATA LOCAL 1 NAME (A20) 1 NAMEIND (I2) END-DEFINE SELECT * INTO NAME INDICATOR NAMEIND ... In this example, If a null indicator field has been specified and the column to be
retrieved is null, the value of the null indicator field is negative and the
target field is set to When specified with a sending
Example: DEFINE DATA LOCAL 1 NAME (A20) 1 NAMEIND (I2) UPDATE ... SET NAME = :NAME INDICATOR :NAMEIND WHERE ... In this example, An |
LINDICATOR
|
LINDICATOR Clause:
The When specified with a receiving
If the Example DEFINE DATA LOCAL 1 ADDRESSLIND (I2) 1 ADDRESS (A50/1:6) END-DEFINE SELECT * INTO :ADDRESS(*) LINDICATOR :ADDRESSLIND ... In this example, When specified with a sending
Example: DEFINE DATA LOCAL 1 NAMELIND (I2) 1 NAME (A20) 1 AGE (I2) END-DEFINE MOVE 4 TO NAMELIND MOVE 'ABC%' TO NAME SELECT AGE INTO :AGE WHERE NAME LIKE :NAME LINDICATOR :NAMELIND ... A If the If the field is defined as Let a fixed length field be defined with a |
The Natural format of a host-variable is converted to an SQL data type according to the following table:
Natural Format/Length | SQL Data Type |
---|---|
An |
CHAR (n) |
B2 |
SMALLINT |
B4 |
INT |
Bn ;
n not equal to 2 or 4
|
CHAR (n) |
F4 |
REAL |
F8 |
DOUBLE PRECISION |
I2 |
SMALLINT |
I4 |
INT |
Nnn.m |
NUMERIC
(nn+m,m) |
Pnn.m
|
NUMERIC
(nn+m,m) |
T |
TIME |
D |
DATE |
Gn ; for view fields
only
|
GRAPHIC (n) |
Natural does not check whether the converted SQL data type is compatible to the database column. Except for fields of format N, no data conversion is done.
In addition, the following extensions to standard Natural formats are available with Natural SQL:
A one-dimensional array of format A can be used to support
alphanumeric columns longer than 253 bytes. This array must be defined
beginning with index 1 and can only be referenced by using an asterisk (*) as
the index. The corresponding SQL data type is CHAR
(n)
, where
n
is the total number of bytes in the
array.
A special host-variable
indicated by the keyword LINDICATOR
can be used to support
variable-length columns. The corresponding SQL data type is VARCHAR
(n)
; see also the
LINDICATOR
clause.
The Natural formats date (D) and time (T) can be used with Entire Access and will be converted into the corresponding database-dependent formats (see the Entire Access documentation for details)
A sending field specified as one-dimensional array without a
LINDICATOR
field is converted into the SQL data type
VARCHAR
. The length is the total number of bytes in the array, not
taking into account trailing blanks.