Using SINGULAR

Go Up to Using Comparison Operators in Expressions

SINGULAR tests that for a given value there is exactly one qualifying row meeting the search condition specified in a subquery. To select all columns, the SELECT clause in the subquery must use the * (asterisk). The complete syntax for SINGULAR is:

[NOT] SINGULAR (SELECT * FROM <tablelist> WHERE <search_condition>)

The following cursor retrieves all countries with a single capital:

EXEC SQL
DECLARE SINGLE_CAPITAL CURSOR FOR
SELECT COUNTRY
FROM COUNTRIES COU
WHERE SINGULAR (SELECT * FROM CITIES CIT
WHERE CIT.CITY = COU.CAPITAL);

Use NOT SINGULAR to retrieve rows that do not meet the qualifying condition specified in the subquery. For example, the following cursor retrieves all countries with more than one capital:

EXEC SQL
DECLARE MULTI_CAPITAL CURSOR FOR
SELECT COUNTRY
FROM COUNTRIES COU
WHERE NOT SINGULAR (SELECT * FROM CITIES CIT
WHERE CIT.CITY = COU.CAPITAL);

For more information about subqueries, see Using Subqueries.

Advance To:

• Determining Precedence of Operators