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
value-specification ::= {literal|dynamic-parameter|CURRENT_DATE|CURRENT_TIME |CURRENT_TIMESTAMP|CURRENT_USER}
(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.
(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.