AW: SQL Sub Sort

Hi,

I'd solve it with 2 cursors. With the first cursor I'd read all status 1 and
for each row selected by this cursor I'd read the the rest of the lines for
this status 1 line. If you want to solve it with a single SQL-Statement you
might have a look at UNION to merge the two "cursors".

I also do not remember if SQL can read two different logicals of the same

physical, which would complicate field identification.

First SQL never reads a logical file, only the physical file. Access Paths
(either in SQL indexes or DDS described keyed logical file) are only used to
get access to the data in the physical files. If you specify a logical file
in an SQL Statement, the SQL statement gets rewritten based on the physical
files, considering the where and join clauses. In an second step the optimal
access paths is determined by the optimizer from all existing access path
defined over the physical file. The access path finally used may or may not
be the access paths defined in the DDS described logical file. 

Also specifying a logical file in a SQL-Statement cause the query dispatcher
to reroute the execution of the SQL-Statement to the old (classic) query
engine, i.e. it cannot profit from the advantages of the new SQL Query
engine. This rerouting may cost up to 15% performance.

But it is possible to specify the same physical file several times in the
same SQL-statement, i.e. it can be joined by itself. To distinguish between
these physical files, you have to use a correlation variable, i.e. you
rename the file temporarily and qualify the field names with this
correlation variable.


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 



-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Al Mac
Gesendet: Monday, January 22, 2007 02:31
An: midrange-l@xxxxxxxxxxxx
Betreff: 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