Scalar-fullselect

A scalar-fullselect as supported in an expression is a fullselect, enclosed in parentheses, that returns a single row consisting of a single column value. If the fullselect does not return a row, the result of the expression is the null value. If more than one row is to be returned for a scalar fullselect, an error occurs.


>>-(--fullselect--)--------------------------------------------><

If a set operator is not specified in the outermost fullselect and the select list element is an expression that is simply a column name, the result column name is based on the name of the column. Otherwise, the result column is unnamed.

Start of change If a column mask is used to mask the column values in the final result table, and if a column mask is applied to the column in the select list of a scalar-fullselect, the result of the scalar-fullselect must not be derived using set operators. See Queries for more information about how column access controls affect a fullselect. End of change

A scalar fullselect cannot be used in the following instances:

A CHECK constraint in CREATE TABLE and ALTER TABLE statements
A CREATE VIEW statement where the view definition includes the WITH CHECK option
A CREATE FUNCTION (SQL) statement (subselect already restricted from the expression in the RETURN clause)
An argument in a CALL statement for an input parameter
An argument to an aggregate function, other than the XML-expression argument of the XMLAGG function
An ORDER BY clause
A GROUP BY clause
A join-condition of the ON clause for INNER and OUTER JOINs

If the scalar fullselect is a subselect, it is also referred to as a scalar subselect. See subselect for more information.

The following examples illustrate the use of scalar-fullselect. Assume that four tables (PARTS, PRODUCTS, PARTPRICE, and PARTINVENTORY) contain product data.

Example 1 - scalar-fullselect in a WHERE clause:

Find which products have the prices in the range of at least twice the lowest price of all the products and at most half the price of all the products.

   SELECT PRODUCT, PRICE FROM PRODUCTS A 
       WHERE
        PRICE BETWEEN 2 * (SELECT MIN(PRICE) FROM PRODUCTS)
              AND .5 * (SELECT MAX(PRICE) FROM PRODUCTS);

Example 2 - scalar-fullselect in a SELECT list:

For each part, find its price and its inventory.

   SELECT PART,  
         (SELECT PRICE FROM PARTPRICE WHERE PART=A.PART),
         (SELECT ONHAND# FROM INVENTORY WHERE PART=A.PART)
       FROM PARTS A;