Basic Syntactical Items

VALUES (1,'A')    leads to a syntax error 
VALUES (1,2,3)    leads to wrong results

VALUES (1 ,'A') 
VALUES (1 ,2 ,3)

Syntax:

[[location-name.]authorization-identifier.]ddm-name

The item table-name in this section is used to reference both SQL base tables and SQL viewed tables.

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.

This optional item specifies the location of the table to be accessed.

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 authorization-identifier is separated from the table name by a period. Using this form, the name of the DDM must be the same as the name of the database table without the authorization-identifier.

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 authorization-identifier as part of the DDM name. The DDM name then consists of the authorization-identifier and the database table name separated by a hyphen (-). The hyphen between the authorization-identifier and the table name is converted internally into a period.

Note:
This form of DDM name can also be used with a FIND or READ statement, because it conforms to the DDM naming conventions applicable to these statements.

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 authorization-identifier has been specified neither explicitly nor within the DDM name, it is determined by the SQL database system.

In addition to being used in SELECT statements, table names can also be specified in DELETE, INSERT and UPDATE statements.

... 
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 
...

sql-type belongs to the SQL Extended Set.

If an sql-type is specified, it has to be surrounded by colons (:).

Valid sql-types are:

A host-variable is a Natural user-defined variable (no system variable) which is referenced in an SQL statement. It can be defined either as an individual field or as part of a Natural view. Under certain circumstances (multifetch into Natural arrays, MERGE statement), it is also possible to specify fixed one-dimensional array ranges such as array(*) or array(1:5). See also compiler option DB2ARRY.

When defined as a receiving field (for example, in the INTO clause), a host-variable identifies a variable to which a value is assigned by the database system.

When defined as a sending field (for example, in the WHERE clause), a host-variable specifies a value to be passed from the program to the database system.

See also Natural Formats and SQL Data Types.

To comply with SQL standards, a host-variable can also be prefixed by a colon (:). When used with flexible SQL, host-variables must be qualified by colons.

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 host-variable or a column can be referenced, the use of a name without a colon is interpreted as a reference to a column.

The INDICATOR clause is an optional feature to distinguish between a "null" value (that is, no value at all) and the actual values 0 or "blank".

When specified with a receiving host-variable (target field), the INDICATOR host-variable (null indicator field) serves to find out whether a column to be retrieved is "null".

Example:

DEFINE DATA LOCAL 
1 NAME      (A20) 
1 NAMEIND   (I2) 
END-DEFINE 
SELECT * 
  INTO NAME INDICATOR NAMEIND 
...

In this example, NAME represents the receiving host-variable and NAMEIND the null indicator field.

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 0 or "blank" depending on its data type. Otherwise, the value of the null indicator field is greater than or equal to 0.

When specified with a sending host-variable (source field), the null indicator field is used to designate a null value for this field.

Example:

DEFINE DATA LOCAL 
1 NAME      (A20) 
1 NAMEIND   (I2) 
UPDATE ... 
SET NAME = :NAME INDICATOR :NAMEIND 
WHERE ...

In this example, :NAME represents the sending host-variable and :NAMEIND the null indicator field. By entering a negative value as input for the null indicator field, a null value is assigned to a database column.

An INDICATOR host-variable is of format/length I2.

The LINDICATOR clause is an optional feature which is used to support columns of varying lengths, for example, VARCHAR or LONG VARCHAR type.

When specified with a receiving host-variable (target field), the LINDICATOR host-variable (length indicator field) contains the number of characters actually returned by the database into the target field. The target field is always padded with blanks.

If the VARCHAR or LONG VARCHAR column contains more characters than fit in the target field, the length indicator field is set to the length actually returned (that is, the length of the target field) and the null indicator field (if specified) is set to the total length of this column.

Example

DEFINE DATA LOCAL 
1 ADDRESSLIND (I2) 
1 ADDRESS     (A50/1:6) 
END-DEFINE 
SELECT * 
  INTO :ADDRESS(*) LINDICATOR :ADDRESSLIND 
  ...

In this example, :ADDRESS(*) represents the target field which receives the first 300 bytes (if available) of the addressed VARCHAR or LONG VARCHAR column, and :ADDRESSLIND represents the length indicator field which contains the number of characters actually returned.

When specified with a sending host-variable (source field), the length indicator field specifies the number of characters of the source field which are to be passed to the database.

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 LINDICATOR host-variable is of format/length I2 or I4. For performance reasons, it should be specified immediately before the corresponding target or source field; otherwise, the field is copied to the temporary storage at runtime.

If the LINDICATOR field is defined as an I2 field, the SQL data type VARCHAR is used for sending or receiving the corresponding column. If the LINDICATOR host-variable is specified as I4, a large object data type (CLOB/BLOB) is used.

If the field is defined as DYNAMIC, the column is read in an internal loop up to its real length. The LINDICATOR field and the Natural system variable *LENGTH are set to this length. In case of a fixed length field, the column is read up to the defined length. In both cases, the field is written up to the value defined in the LINDICATOR field.

Let a fixed length field be defined with a LINDICATOR field specified as I2. If the VARCHAR column contains more characters than fit into this fixed length field, the length indicator field is set to the length actually returned and the null indicator field (if specified) is set to the total length of this column (retrieval). This is not possible for fixed length fields greater than or equal to 32 KB (length does not fit into null indicator field).