RE: Replace CHAIN with SQL

If that doesn't do what you want, try:

With tmpDetail as (select id, min(sequence) as seq from detail group by id)
Select a.*, c.*, d.* from header a
Join tmpDetail b on a.id = b.id
Join detail c on b.id = c.id and b.seq = c.sequence
Join thirdfile d on whatever

The SQL below will give you all the headers that have a customer in the detail, but no other data from
the details.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Michael_Schutte@xxxxxxxxxxxx
Sent: Thursday, April 24, 2008 8:16 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Replace CHAIN with SQL

I need to read the whole thing...

with tmpDetail as (select distinct customer from detail)
Select * from header a
join tmpDetail b on a.customer = b.customer
join thirdfile on whatever.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.





rpg400-l-bounces@xxxxxxxxxxxx wrote on 04/24/2008 08:11:44 AM:

Hi,

I'd like to find an equivalent for RPG CHAIN so that I can use it in
interactive SQL.

I'm trying to display the records that my RPG program extracts with
a CHAIN. The CHAIN uses a partial key to return the first record
only for that key.

How do you join a header file to the first record of the detail file
while still being able to join to a 3rd file ?

I mean, how to list all the customers and the information of the
first record only of detail file for each customer.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.