SQL Sub Sort

   I am using SQL embedded in RPG/400 to generate data sequenced by stage of
   manufacturing, and sequential number of instructions how to manufacture
   it.  The first text line of each stage is an overall description, and that
   first line is also "status" type code "1".  They want me to resequence
   this, so each group of instructions on a mfg stage is sorted by that
   status "1" description, but continue rest of text lines that come after
   each "1" to remain in that sequence.  There's nothing to tell software
   that this bunch of text lines "goes with" a first description, other than
   being sequential operation #s until the next status type"1" line I do not
   know if this is doable, or how to do it.  I can do ILE, I am just not good
   at it.

   Data formatted
     * status "1" description is of what we are doing in each stage of 
       manufacturing, with an operation # like 100
     * then there are a bunch of text lines of other than status "1", with
       operation #s like 101 102 103 etc. that print after the status "1" ...
       we only print until hit another status "1", which might be operation
       150
     * right now I am printing this in operation sequence, then when it hits
       a new status "1", doing like a control break
     * Then humans physically sort my output so it is organized by the type
       of work to be done, which takes them hours.  They want the computer to
       pre-sort for them.
   Here's how the code currently reads

   /EXEC SQL                                                        
   DECLARE C1 CURSOR FOR SELECT                                   
   
SORD,SQREQ,SOFAC,IPROD,IDESC,IFII,IITYP,ICLAS,OOPNO,OWRKC,OOPDS,OTOOL,OOPER,OSTAT,SORTEM,OCDTE
                                   
   FROM FSO,IIM,FRT,FOD WHERE SID='SO' AND IID='IM' AND           
   RID='RT' AND ODID IN('OD','RD') AND SPROD=IPROD AND SPROD=RPROD AND
   SORD=OORD AND ROPNO=OOPNO AND OSTAT IN('1','3')            
   GROUP BY                                        
   
SORD,SQREQ,SOFAC,IPROD,IDESC,IFII,IITYP,ICLAS,OOPNO,OWRKC,OOPDS,OTOOL,OOPER,OSTAT,SORTEM,OCDTE
                                   
   ORDER BY SORD,OOPNO,OSTAT                                      
   /END-EXEC                                                        

   For those of you that recognize this as BPCS ... we are version 405 CD  It
   has been a while since I modified this. I not see right now what role
   master FRT file is playing, which is where FOD file got the data needed
   for current shop order.

   Description FOD.OOPDS & FRT.ROPDS
   Status FOD.OSTAT & FRT.RSTAT
   Operation FOD.OOPNO & FRT.ROPNO
   Shop Order only in Order files  FSO.SORD & FOD.OORD
   Parent Item in several files, includes FRT.RPROD & FSO.SPROD

   I also do not remember if SQL can read two different logicals of the same
   physical, which would complicate field identification.

   -
   Al Macintyre
   http://en.wikipedia.org/wiki/User:AlMac
   http://www.ryze.com/go/Al9Mac
   BPCS/400 Computer Janitor ... see
   http://radio.weblogs.com/0107846/stories/2002/11/08/bpcsDocSources.html