× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.