Correlating Subqueries

The ex7a.sqr sample program contains three procedures—main, cash_receipts, and orders—that correspond to the three queries. The main procedure is the master. It retrieves the customer names. For each customer, the program invokes the cash_receipts procedure to list the cash receipts, if any, and the orders procedure to list the customer’s orders, if any.

The procedures take the cust_num variable as an argument. As you can see, cash_receipts and orders are called many times, once for each customer. Each time, the procedures perform the same query with a different value for the cust_num variable in the WHERE clause.

Note the use of the IF command and the #any numeric variable in these procedures. When the BEGIN-SELECT command returns no records, SQR does not process the PRINT commands that follow. Thus, the headings for these procedures appear only for those customers who have records in the detail tables.

The orders procedure demonstrates the use of an expression in the BEGIN-SELECT command. The expression is c.price * b.quantity.

Note: Examine the format of the dollar amount with the argument EDIT $$$$,$$0.99. This format uses a “floating-to-the-right” money symbol. If fewer digits are used than the six that we specified here, the dollar sign floats to the right and remains close to the number.

See Using Variables in SQL.

The following is the output for program ex7a.sqr:

6-APR-2004                                                  Page 1

Customer Information
---------------------------------------------
Gregory Stonehaven        Everretsville    OH

         Cash Received
         -------------
         01-FEB-03                $130.00

Customer Information
---------------------------------------------
John Conway               New York         NY

         Cash Received
         -------------
         01-MAR-03                $140.00

Customer Information
---------------------------------------------
Eliot Richards            Queens           NY

         Cash Received
         -------------
         16-JAN-03                $220.12
         17-JAN-03                $260.00

         Orders Booked
         -------------
         02-MAY-03            Whirlybobs               $239.19
         02-MAY-03            Canisters              $3,980.25

Customer Information
---------------------------------------------
Isaiah J Schwartz and Com Zanesville       OH

         Cash Received
         -------------
         18-JAN-03                $190.00
         02-JAN-03              $1,100.00

         Orders Booked
         -------------
         02-MAY-03            Hop scotch kits        $6,902.00
         02-MAY-03            Wire rings            $19,872.90  

Customer Information
---------------------------------------------
Harold Alexander Fink     Davenport        IN         

         Cash Received
         -------------
         01-FEB-03              $1,200.00
         01-MAR-03              $1,300.00         

         Orders Booked
         -------------
         19-MAY-03            Ginger snaps              $44.28
         19-MAY-03            Modeling clay            $517.05