RE: SQL question

Rich,

Everybody's pretty much told you this isn't going to work due to the use
of a expression on the columns in the where clause.

I can think of two possibilities....

1) Perhaps a DDS logical keyed on a new date field that concats the
current fields.  I'm not sure if this would really help.

2) Build upon the idea that you can get it too work by specifying all
dates in the where clause.  Perhaps a Table UDF that outputs the dates
in the selected range.  The you can join the table UDF to the original
data.  Figuring out the best way to code the table UDF will be fun. :-)

Also, note that if you limit the select date range to a given month,
then you only need where year = 2005 and month = 10, you don't need to
specify all the days.  Can you have any such limitations or do you need
to allow any begin date to any end date?


HTH, 


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rich Duzenbury
Sent: Friday, October 28, 2005 4:12 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL question

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