Re: SQL question

How about using:

Select .....
>From .....
Where  digits(DATE_YY) || digits(DATE_MM) || digits(DATE_DD)
     between '20050101' and '20051028'


Bill Milyard
614-497-4712
614-409-2016 (Fax)


                                                                           
             Rich Duzenbury                                                
             <rduz-midrange@we                                             
             sternmidrange.com                                          To 
             >                         midrange-l@xxxxxxxxxxxx             
             Sent by:                                                   cc 
             midrange-l-bounce                                             
             s@xxxxxxxxxxxx                                        Subject 
                                       SQL question                        
                                                                           
             10/28/2005 04:11                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.