--- In firebird-support@yahoogroups.com, "sboydlns" wrote:

> > I think
> >
> > SELECT *
> > FROM GLJOUR
> > WHERE Company >= :Company
> > AND (Company > :Company
> > OR (Account >= :Account
> > AND (Account > :Account
> > OR (Division >= :Division
> > AND (Division > :Division
> > OR (Department >= :Department
> > AND (Department > :Department
> > OR (EffectiveDate >= :EffectiveDate
> > AND (EffectiveDate > :EffectiveDate
> > OR ControlNumber >= :ControlNumber)))))))))
> >
> > will achieve the same thing with lesser code.
>
> Actually I don't think that will give me what I want. Consider:
>
> I want to start reading start at Company = 10 and Account = 1000.
> That means I should get all of Company 10 where Account >= 1000 and
> all of Company 11, 12, 13, ... irregardless of the value of Account.
>
> That is I should get 10-1000, 10-1001, ... and 11-0001, 11-0002, ...
>
> I don't think that the above WHERE clause will give me that.

I think that's exactly what it will return. Let's replace the parameters with numbers:

SELECT *
FROM GLJOUR
WHERE Company >= 10
AND (Company > 10
OR (Account >= 1000
AND (Account > 1000
OR (Division >= ...

So, company < 10 will not be returned, > 10 will be returned regardless, whereas = 10 will be returned if the account field is > 1000. If account = 1000 it will depend on any possible criteria for division and so on.

Of course, you would have to add ORDER BY and preferrably reduce the result set a bit through using ROWS (or FIRST, but ROWS is standard and FIRST Firebird specific). I'm sure you could agree on some sensible number of rows with your employer, even people used to ISAM files know they rarely need millions of rows returned.

I liked Philips idea of using a computed field, although whilst I think it would be the best solution for CHAR (and VARCHAR) fields (at least as long as they all use the same character set and collation), I fear it could be cumbersome when some fields are numbers.

HTH,
Set