-- Initialize variables
SET VAR vCustState TEXT, vCustid TEXT
-- Set up the breakpoints with cursors.
-- Cursor c1 is break 1, cursor c2 is break 2,
-- cursor c3 retrieves the detail data.
DECLARE c1 CURSOR FOR +
SELECT DISTINCT custstate FROM customer
DECLARE c2 CURSOR FOR +
SELECT DISTINCT custid FROM transmaster +
WHERE custid IN +
(SELECT custid FROM customer +
WHERE custstate = .vCustState)
DECLARE c3 CURSOR FOR +
SELECT transid, empid, invoicetotal, transdate +
FROM transmaster WHERE custid = .vCustid
OPEN c1
FETCH c1 INTO vCustState Ind1
WHILE SQLCODE <> 100 THEN
-- This is the break header 1 position,
-- the first state value is retrieved.
-- Cursor c2 then fetches all customer rows
-- for that particular state
OPEN c2 RESET
FETCH c2 INTO vCustid Ind2
WHILE SQLCODE <> 100 THEN
-- This is the break header 2 position,
-- the first customer row. Cursor c3 then
-- fetches all transaction rows for
-- that customer
OPEN c3 RESET
FETCH c3 INTO vTransid vind1, vEmpid vind2, +
vInvoiceTotal vind3, vTransDate vind4
WHILE SQLCODE <> 100 THEN
-- The detail rows are processed here
FETCH c3 INTO vTransid vind1, vEmpid vind2, +
vInvoiceTotal vind3, vTransDate vind4
ENDWHILE
-- Break footer 2 position is here, +
-- right before the next
-- customer row is fetched.
FETCH c2 INTO vCustid Ind2
ENDWHILE
-- Break footer 1 position is here, after
-- all the customer data has been fetched,
-- before the next state value is fetched.
FETCH c1 INTO .vCustid
ENDWHILE
