> So supposed you want to start at company 'CO', account 'ACCT'
> and read all records from that point to the end of the file.

If I understand you correctly, then:

SELECT *
FROM GLJOUR
WHERE (Company >= :Company) OR
(Company >= :Company AND Account >= :Account)
ORDER BY Division, Department, Effective Date, Control Number

You haven't said explicity that you need to filter out Division, Department, Effective Date, Control Number - rather that you want to read from a point onwards.

Even if you do need to restrict at those levels, this can be performed either on the server or the client. If on the server, you can optionally set up a stored procedure for the key part of the sql (i.e. everything except the ORDER BY clause) and then pass in parameters for Division, Department, Effective Date, Control Number (or alternatively have distinct sps for each flavour of extraction you might want).

Kind regards,
Tom

--- In firebird-support@yahoogroups.com, "sboydlns" <sboydlns@...> wrote:
>
> I am migrating a bunch of existing ISAM files into Firebird. In order to make the migration easier for the programmers we want to be able to reproduce the ISAM structure and behaviour as closely as possible.
>
> The ISAM files have a lot of composite keys. Consider the GL journal entry file. It has a primary key of:
>
> Company
> Account
> Division
> Department
> Effective Date
> Control Number
>
> A typical thing that would be done in the existing programs would be to seek to some point in the file and read from there to the end of the file. So supposed you want to start at company 'CO', account 'ACCT' and read all records from that point to the end of the file. In an ISAM file this is easy to do. You set up your key by concatenating the individual fields and return all records whose key is >= to the concatenated fields. So, in this example you would return all records where the key >= 'COACCT' (which is the company and account codes strung together).
>
> This is not so easy to emulate with a SELECT statement. I have come up with this:
>
> SELECT *
> FROM GLJOUR
> WHERE Company > :Company OR
> (Company = :Company AND Account > :Account) OR
> (Company = :Company AND Account = :Account AND Division > :Division) OR
>
> and so on and so on and so on ...
>
> Another alternative would be to create a single column in the table which is the concatenation of all of the columns making up the composite key and using that as the key. That is to say, make a single column key rather than a composite key. I would still want to retain the individual key fields as separate columns in the database so I would in effect be duplicating the data. That makes the relational purist in me shudder but it would get the job done and be considerably less complicated than dealing with the composite key.
>
> Are there any other options that I have failed to consider? If it were you, how would you handle it?
>