""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   GET MORE OUT OF A SINGLE DECLARE CURSOR COMMAND
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   PRODUCT   :  R:BASE                  VERSION      :  3.1
   CATEGORY  :  PROGRAMMING             SUBCATEGORY  :  TOOLS
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   From Bill Downall, 5411 White Willow Court, Indianapolis, IN 46254-
   9633. Bill is a database consultant and educator. You can reach him at
   317-297-3810 or through David M. Blocker and Associates at 617-784-
   1919.
 
   The DECLARE CURSOR command has the full power of the SELECT command
   embedded in it, so you can do many jobs at once. You may be able to
   speed up WHILE loops and reduce the need for time-consuming memory
   swaps by reducing the number of different commands you use.
 
   For example, you can improve the performance of some cursor processing
   loops by using the SELECT clause in the DECLARE CURSOR to replace
   lookups that use SET VAR processing. The example below shows you how
   to optimize a DECLARE CURSOR structure by folding other commands into
   the DECLARE CURSOR itself.
 
 
   Original DECLARE CURSOR
   """""""""""""""""""""""
   The following block of code uses these four commands: DECLARE CURSOR,
   FETCH, SET VAR, and SELECT:
 
     DECLARE c1 CURSOR AS SELECT +
       idnum, +
       fn, +
       mi, +
       ln, +
       zipcode +
       FROM people +
       ORDER BY ln, fn, mi
     OPEN c1
     FETCH c1 INTO +
       vidnum, +
       vfn, +
       vmi IND nomi, +
       vln, +
       vzip IND nozip
     WHILE SQLCODE <> 100 THEN
       *( Build a full name from the pieces.)
       SET VAR vfullname =  (.vfn & .vmi & .vln)
       *( Look up city & state for this zip.)
       SELECT city, state INTO +
         vcity IND nocity, +
         vstate IND nostate +
         FROM cszs +
         WHERE zipcode = (.vzip)
       *( ...many other commands here...)
       FETCH c1 INTO vidnum, vfn, +
         vmi IND nomi, vln, vzip IND nozip
     ENDWHILE
     DROP CURSOR c1
 
 
   Optimized DECLARE CURSOR
   """"""""""""""""""""""""
   By folding the SET VAR and SELECT commands into the original DECLARE
   CURSOR, you can accomplish the same thing using only DECLARE CURSOR
   and FETCH:
 
     DECLARE c1 CURSOR AS SELECT +
       p1.idnum, +
       (p1.fn & p1.mi & p1.ln), +
       p1.zipcode, +
       z1.city, +
       z1.state +
       FROM people p1, cszs z1 +
       WHERE p1.zipcode = z1.zipcode +
       ORDER BY p1.ln, p1.fn, p1.mi
     OPEN c1
     FETCH c1 INTO +
       vidnum, +
       vfullname, +
       vzip, +
       vcity IND nocity, +
       vstate IND nostate
     WHILE SQLCODE <> 100 THEN
       *( ...many other commands go here...)
     FETCH c1 INTO +
       vidnum, +
       vfullname, +
       vzip, +
       vcity IND nocity, +
       vstate IND nostate
     ENDWHILE
     DROP CURSOR c1
 
   In this optimized DECLARE CURSOR structure, the SELECT clause joins
   the two tables (PEOPLE and CSZS) together. The WHERE clause (WHERE
   p1.zipcode = z1.zipcode) automatically ensures that only rows with
   existing zip codes are included, so you no longer need an INDICATOR
   variable for VZIP. SQL automatically rejects the null values when
   performing the join.
 
   Make sure the column ZIPCODE is an indexed column. SQL uses indexes to
   speed up joins.
 
   The SELECT clause can select data from multiple tables, group rows,
   calculate expressions, append rows together, and load variables with
   values. By using the full power of the SELECT clause in DECLARE
   CURSOR, INSERT, CREATE VIEW, and SELECT commands, you may be able to
   reduce code and increase speed.