Re: SQL multiple selects

[rob@xxxxxxxxx]

Traditionally this is done using only 1 sql statement.  Again using 
between.
D @SQLByCat       C     'Select * from dmitmmst +
D                        Where (WHSLC between +
D                        ? and ?) and +
D                        (CATCD1 between ? and ?) +
D                        Order By SAQYR desc'
If you want only 1 value for CATCD1 then you use that for both parameters. 
 If you want all, then use *loval and *hival.
Now, I am assuming you are using quotes around the substitution parameter 
for CATCD1 because it is a character.  Like:
CREATE TABLE QTEMP/DMITMMST
 (WHSLC NUMERIC ( 2, 0) NOT NULL WITH DEFAULT,
 CATCD1 CHAR ( 1) NOT NULL WITH DEFAULT,
 SAQYR NUMERIC (4 ,0) NOT NULL WITH DEFAULT)  
INSERT INTO QTEMP/DMITMMST VALUES(1, '1', 1994)
INSERT INTO QTEMP/DMITMMST VALUES(2, '2', 2001)
The quotes are not necessary.  This is actual working code:
     H ActGrp(*CALLER)
     H DftActGrp(*NO)

     D OpenCursor      PR              n
     D FetchCursor     PR              n
     D CloseCursor     PR              n

     D @SQLByCat       S          32000a   inz(
     D                                     'Select * from dmitmmst +
     D                                      Where +
     D                                      (WHSLC between ? and ?) and +
     D                                      (CATCD1 between ? and ?) +
     D                                      Order By SAQYR desc')
     D MyLib           s             10a
     D MyFile          s             10a
     D dmitmmst      e ds                  extname(dmitmmst)
     D whslca          s                   like(whslc) inz(1)
     D whslcb          s                   like(whslc) inz(2)
     D catcd1a         s                   like(catcd1) inz(*loval)
     D catcd1b         s                   like(catcd1) inz(*hival)

      /free
       *inlr=*on;
       if not OpenCursor();
         // perform error routine to alert the troops
         // ...
       Else;
         Dow FetchCursor();
           // putting the fetchcursor on the do loop allows the user of
           // iter, and thus iter will not perform an infinite loop
           // normal processing here...
         EndDo;
         CloseCursor();
       EndIf;
       return;
      /end-free

     P OpenCursor      B
     D OpenCursor      PI                  like(ReturnVar)
     D ReturnVar       s               n

     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     Datfmt    = *iso,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC
      /free
       dsply sqlstt;
      /end-free
     C/EXEC SQL
     C+ Prepare s1 from :@SQLByCat
     C/END-EXEC
      /free
       dsply sqlstt;
      /end-free
     C/EXEC SQL
     C+ Declare C1 cursor for S1
     C/END-EXEC
      /free
       dsply sqlstt;
      /end-free

     C/EXEC SQL
     C+ Open C1 using :whslca, :whslcb, :catcd1a, :catcd1b
     C/END-EXEC
      /free
       dsply sqlstt;
      /end-free

      /free
       Select;
         When SqlStt='00000';
           return *on;
         Other;
           return *off;
       EndSl;
      /end-free
     P OpenCursor      E

      /eject
     P FetchCursor     B
     D FetchCursor     PI                  like(ReturnVar)
     D ReturnVar       s               n
     C/EXEC SQL
     C+ Fetch C1 into :dmitmmst
     C/END-EXEC
      /free
       Select;
         When sqlstt='00000';
           // row was received, normal
           ReturnVar=*on;
         When sqlstt='02000';
           // same as %eof, sooner or later this is normal
           ReturnVar=*off;
         Other;
           // alert the troops!
           ReturnVar=*off;
       EndSl;
       return ReturnVar;
      /end-free
     P FetchCursor     E

      /eject
     P CloseCursor     B
     D CloseCursor     PI                  like(ReturnVar)
     D ReturnVar       s               n
     C/EXEC SQL
     C+ Close C1
     C/END-EXEC
      /free
       Select;
         When sqlstt='00000';
           // cursor was closed, normal
           ReturnVar=*on;
         Other;
           // alert the troops!
           ReturnVar=*off;
       EndSl;
       return ReturnVar;
      /end-free
     P CloseCursor     E 

If I change this
     D catcd1a         s                   like(catcd1) inz(*loval)
     D catcd1b         s                   like(catcd1) inz(*hival)
to this:
     D catcd1a         s                   like(catcd1) inz('1')
     D catcd1b         s                   like(catcd1) inz('1')
It works just fine.

Rob Berendt