SQL question

[Rich Duzenbury <rduz-midrange@xxxxxxxxxxxxxxxxxxx>]

Hi all,

I've got a table with millions and millions of records.  Said table was
built by the vendor with each date being separated into components like:

DATE_YY  4S 0
DATE_MM  2S 0
DATE_DD  2S 0

>From RPG, using the logical file keyed by the three date components, one
can use SETLL with a READ loop to quickly select a set of records.

In SQL, because of the index, queries like

SELECT * FROM TABLE WHERE DATE_YY = 2005 and DATE_MM = 10 and DATE_DD =
27

work very well.


What I can't seem to figure out is how to use SQL to select a date range
that doesn't result in a table scan.

These result in a table scan:
SELECT DATE_YY * 10000 + DATE_MM * 100 + DATE_DD FROM TABLE WHERE
       (DATE_YY * 10000 + DATE_MM * 100 + DATE_DD) >= 20051001 AND
       (DATE_YY * 10000 + DATE_MM * 100 + DATE_DD) <= 20051028

SELECT DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) FROM TABLE
WHERE (DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) >=
'20051001' AND
(DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) <= '20051028'


This one doesn't, but it's problematic:

SELECT DIGITS(DATE_YY) || DIGITS(DATE_MM) || DIGITS(DATE_DD) FROM TABLE
WHERE
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 1) OR
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 2) OR
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 3) OR
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 4) OR
      ...
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 25) OR
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 26) OR
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 27) OR
    (DATE_YY = 2005 AND DATE_MM = 10 AND DATE_DD = 28)

Each date has to be included in the where clause.  If there are very
many dates, I can see how it would be fairly easy to exhaust even a very
large string.

RPG works well with the current index.
Because of the way the file is setup with the components laid out in the
record as YYYYMMDD, the old FMTDTA command works well.
I'd like to have SQL work just as well.

Any ideas appreciated.  Oh, and I can't change the file!  I doubt that I
can get away with adding any more logicals, but I will take that under
advisement.

Thank you.

Regards,
Rich