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.
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