Re: SQL statement to read previous records

On 18-May-2016 08:09 -0500, Bill Howie wrote:

On 18-May-2016 07:59 -0500, Denis Robitaille wrote:

On 18-May-2016 07:52 -0500, Bill Howie wrote:

I'm working on a project that requires the use of SQL. What I
have is a situation where there can be a group of records in a
file where the first record in a "set" of records is the "main"
record and the subsequent records describe attributes of that
"main" record.
There is a record type field in this file which is used to
differentiate between the main record and the "attribute"
records.
So what I'll have in the file is a "main" record, then a bunch of
"attribute" records, then another "main", then "attributes", and
so on.

There is no field in the "attribute" record that links it back
to the "main" record it goes with. The association between the
two is simply the fact that the "attribute" record comes after
the "main" record in the file.
Any thoughts from anyone on an ingenious way to use SQL to grab
the "main" record? I think there has to be a way, just not
coming up with it right now. Any help is appreciated! Thanks!

<ed: addendum from followup:> in this scenario I'd be reading
the "attribute" record with SQL and need some way to find the
"main" record.

Could you post the SQL statement to retrieve the attribute record.
This may help in figuring out a way to do what you need.

Sure, here it is....

select a.*
from oeodtp a
join oeodtp b
on a.odordr = b.odordr
where a.oditem in ('03A0','03A2')
and b.oditem = '975632'

In this scenario the "03A0" and "03A2" records are the "main"
records, and the "975632" is the "attribute" record. The results of
this statement are that it returns records on orders that have both
an "03A0"/"03A2" record and a "975632" record, but not the correct
ones on those orders.

Hope this makes sense.

I can make no sense of "but not the correct ones on those orders."

Also confusing is the suggestion that there "is no field in the 'attribute' record that links it back to the 'main' record" because that seems possibly, to be contradicted by the above join, that appears to link Main-records with their Attr-records using a common key of odOrdr?