Specifying Result Tables › Testing for Existence

Testing for Existence

Use the EXISTS predicate to test for the existence of certain rows. The EXISTS predicate evaluates to true only if the subquery finds a row which meets the specifications of its search condition.

 Problem

Select the state and minimum year-to-date sales for each state, but
only check customers who have orders.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       DECLARE YTDLIST CURSOR FOR
  3           SELECT STATE, MIN(YTD_SALES)
  4           FROM CUSTOMERS
  5           WHERE EXISTS
  6               (SELECT *
  7                FROM ORDERS
  8                WHERE ORDERS.CUST_NO = CUSTOMERS.CUST_NO)
  9           GROUP BY STATE
 10   END-EXEC.
               .
               .
       (COBOL statements)
               .
               .

Line 3

The outer subselect of the SELECT statement specifies what columns to retrieve only if the subselect of the EXISTS predicate (lines 5-8) evaluates to true.

Lines 5-8

The subselect of the EXISTS predicate specifies which conditions must be met in order for the predicate to evaluate to true. In this case, the customer number in the ORDERS table must match a customer number in the CUSTOMERS table.

Line 9

The columns of the result table formed by the outer subselect are grouped by the value of the STATE column, that is to say, the result table contains one row for each unique value of STATE.