7.21.1 Specification format for value specifications

In an SQL statement, the following items can be specified in places where a value specification is allowed:

A literal
A dynamic parameter
The datetime information acquisition function CURRENT_DATE
The datetime information acquisition function CURRENT_TIME
The datetime information acquisition function CURRENT_TIMESTAMP
The user information acquisition function CURRENT_USER

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Examples

(1) Specification format

value-specification ::= {literal|dynamic-parameter|CURRENT_DATE|CURRENT_TIME
                 |CURRENT_TIMESTAMP|CURRENT_USER}

To Page Top

(2) Explanation of specification format

literal:

For details about literals, see 6.3 Literals.

The data type of the result of the value specification will be the data type of the specified literal.

dynamic-parameter:

For details about dynamic parameters, see 6.6 Variables (dynamic parameters).

The data type of the result of the value specification will vary depending on the location where the dynamic parameter is specified.

CURRENT_DATE:

For details about CURRENT_DATE, see 6.4.1 CURRENT_DATE.

The data type of the result of the value specification will be the DATE type.

CURRENT_TIME:

For details about CURRENT_TIME, see 6.4.2 CURRENT_TIME.

The data type of the result of the value specification will be the TIME type.

CURRENT_TIMESTAMP:

For details about CURRENT_TIMESTAMP, see 6.4.3 CURRENT_TIMESTAMP.

The data type of the result of the value specification will be the TIMESTAMP type.

CURRENT_USER:

For details about CURRENT_USER, see 6.5.1 CURRENT_USER.

The data type of the result of the value specification will be VARCHAR type.

To Page Top

(3) Examples

The following examples illustrate value specifications.

Example 1

This example inserts the following data (row) into the sales history table (SALESLIST).

Customer ID (USERID): U00358
Product code (PUR-CODE): P003
Quantity purchased (PUR-NUM): 5
Date of purchase (PUR-DATE): 2011-09-08

INSERT INTO "SALESLIST"
    ("USERID","PUR-CODE","PUR-NUM","PUR-DATE")
    VALUES('U00358','P003',5,DATE'2011-09-08')

The underlined portions show the locations of the value specifications.

Example 2

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID) and product code (PUR-CODE) for customers who purchased products today.

SELECT "USERID","PUR-CODE"
    FROM "SALESLIST"
        WHERE "PUR-DATE"=CURRENT_DATE

The underlined portion indicates the location of the value specification.

To Page Top