SQL-J Language Reference
Page 57 of 121

VALUES expression

The VALUES expression allows construction of a row or a table from other values. You use a VALUES statement when you do not have a FROM clause. This construct can be used in all the places where a query can, and thus can be used as a statement that returns a ResultSet, within expressions and statements wherever subqueries are permitted, and as the source of values for an INSERT statement.

A common use of the VALUES clause is to call a static method that returns a value or to call a method on an object not serialized in the database that returns a value.

Syntax

{
    VALUES ( Value {, Value }* )
        [ , ( Value {, Value }* ) ]* |
    VALUES Value [ , Value ]* |
    VALUES Work Unit Invocation
}

Value

Expression | DEFAULT

The first form constructs multi-column rows. The second form constructs single-column rows, each expression being the value of the column of the row.

The DEFAULT keyword is allowed only if the VALUES expression is in an INSERT statement. Specifying DEFAULT for a column inserts the column's default value into the column. Another way to insert the default value into the column is to omit the column from the column list and only insert values into other columns in the table.

In a Cloudscape synchronization system, you can use the third form of the VALUES clause to invoke a work unit, a type of method alias registered with the system in which only the method call and target parameter values are replicated, not any underlying statements. The parameters are applied at the target when VALUES is applied at the target, and they are saved and sent to the source for use during the next refresh. For more information about work unit invocation, see the Cloudscape Synchronization Guide.

VALUES expression Examples

-- constructing a derived table
VALUES ('orange', 'orange'), ('apple', 'red'),
('banana', 'yellow')

-- inserting multiple rows of multiple columns into a table
INSERT INTO FruitsAndColors (fruit, color)
VALUES ('orange', 'orange'), ('apple', 'red'),
('banana', 'yellow')

-- insert a row with a DEFAULT value for a column
INSERT INTO HotelAvailability
VALUES (110, current_date, DEFAULT)

-- using a built-in function
VALUES CURRENT_DATE

-- getting the value of an arbitrary expression
VALUES (3*29, 26.0E0/3)

-- getting the value returned by a static method
VALUES (CLASS java.lang.Integer).toString(1)

-- calling a method on an object not serialized in the database
VALUES 'Santiago'.toUpperCase()

-- calling a method on an object not serialized in the database
-- if the method does not return a value, use the CALL statement
VALUES NEW JBMSTours.CreateSchema().createSQLScript(
    getCurrentConnection())

-- calling a method alias that returns a value
VALUES findCity(getCurrentConnection(), 'Paris', 'France')

-- at next refresh, work unit invocation is replicated at source,
-- along with parameter values
-- you can access the value returned by the work unit
VALUES myWorkUnit(getCurrentConnection(), ?, ?)