Re: SQL Question -- MIDRANGE-L

I have been successfully using SQL embedded in RPG to select factory shop 
order records from a logical file.  The unique key is shop order number, then 
sequence number of records within the order.  The logical has the stuff in 
the right sequence.  My SQL is told to read record equal to that shop order 
number, then I process the data & do the same SQL subroutine again & it reads 
the next record in sequence for that shop order number, just like SETLL & 
READ if I was doing RPG without SQL in the picture.

Except that when I said "shop order file" I was telling a white lie, I am 
really reading in fields from a whole bunch of files at the same SQL read 
time that in aggregate are like fields that RPG might READ from a single 
file, and they come in in "sequence" order, so I have the choice of reading 
just the first one in sequence, or the whole lot, in sequence.

I am not using a join, but rather each file directly through its logicals.

It is not simple for me to implement.
I have to define the cursor, which sounds like a swear word & I use a few to 
get this right
I have to open the sucker
I have to do the select
I have to close it

When it does not compile, the error messages are clear as mud.
I would have never figured out how to do this were it not for help from Crowe 
Chizek.

>  From:    bvstone@taylorcorp.com (Stone, Brad V (TC))
>  
>  Question on SQL...
>  
>  I have an order detail file.  The unique key is invoice number, sequence
>  number.  I want to only select records that are the first sequence number
>  for each invoice.
>  
>  I've been playing, but I can't figure it out.  Something like (not
>  syntatically correct)
>  
>  select * from orddetpf where odcst = '36000' 
>  order by odinv 
>  but only select min(seqno) group by invoice
>  
>  I'd like to do it with a single select (no subquery) if possible.  This is
>  because I have an option for a user to either view all detail lines, or 
only
>  the first one of each order.  I already have the SQL statment for the 
first,
>  and would like to just modify it (dynamic SQL) depending on which "mode" 
the
>  user chooses.
>  
>  Thanks!
>  
>  Brad


Al Macintyre  ���
http://www.cen-elec.com MIS Manager Programmer & Computer Janitor

Y2K is not the end of my universe, but a re-boot of that old Chinese curse.
This message was written and delivered using 100% post-consumer (recycled) 
data bits
The road to success is always under construction.
Accept that some days you are the pigeon and some days the statue.
Murphy's Mom brought wrong baby home from hospital so it should be Kelly's 
Law.
If consistency is the hobgoblin of little minds, only geniuses work here.
When you want it cheap - you get what you paid for.
When in doubt, read the manual, assuming you can find the right one.
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---