Specifying Result Tables › Calculating Values

Calculating Values

You can use expressions and/or functions in the SELECT statement to calculate values which are not contained in the actual table. Predicates which contain expressions can be used in the WHERE clause to form a search condition based on existing column values.

Example 1

 Problem

Calculate each year-to-date sales after a 10 percent discount.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       DECLARE YTDLIST CURSOR FOR
  3           SELECT YTD_SALES, YTD_SALES * .9
  4           FROM CUSTOMERS
  5   END-EXEC.
               .
               .
       (COBOL statements)
               .
               .

Line 3

The SELECT statement includes the expression YTD_SALES * .9 to calculate the new year-to-date sales after a 10 percent discount.

Example 2

The following example uses nested subselects. The inner subselect is called a subquery. Correlation names are also used in this example to avoid ambiguity in referring to columns.

 Problem
Find all year-to-date sales whose current value is greater than the
average year-to-date sales, and the industry code is A.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       DECLARE YTDLIST CURSOR FOR
  3           SELECT CUST_NO, YTD_SALES
  4           FROM CUSTOMERS C1
  5           WHERE IND_CD = 'A'
  6               AND YTD_SALES >
  7                   (SELECT AVG(YTD_SALES)
  8                    FROM CUSTOMERS
  9                    WHERE CUST_NO = C1.CUST_NO)
 10   END-EXEC.
               .
               .
       (COBOL statements)
               .
               .

Lines 3-6

The outer subselect requests a set of all rows where the IND_CD values is equal to 'A' and the YTD_SALES value is greater than the specifications in the subquery. In Line 4, C1 is the correlation name for the CUSTOMERS table in the outer subselect.

Lines 7-9

The subquery finds the average YTD_SALES where the CUST_NO value equals the CUST_NO value in the outer subselect. C1 is used as a qualifier to indicate the reference is to CUST_NO in the outer subselect.