RE: SQL question -- MIDRANGE-L

One of the replies used the DIGITS() function and still ran a tablescan. This illustrates a basic rule - avoid expressions that usefunctions in the WHERE clause and in JOIN specifications, because itwill ALWAYS result in a table scan (or probably at least 99% of thetime). This is because it cannot eliminate anything without firstrunning the calculation, so it has to run that calculation on ALL the records.


Make sense?

The solution offered here is least likely to use a table scan, as ithas no functions in it.


HTH
Vern

At 03:29 PM 10/28/2005, you wrote:

How about:
(DATE_YY > SelectedYear or
 (DATE_YY = SelectedYear and
  (DATE_MM > SelectedMonth or
   DATE_MM = SelectedMonth and
   DATE_DD >= SelectedDay))) and
(DATE_YY < Selected Year or
 (DATE_YY = SelectedYear and
  (DATE_MM < SelectedMonth or
   DATE_MM = Selected Month and
   DATE_DD <= SelectedDay)))

Out of curiosity, why do you want to use SQL if the RPG code can easily
select the records?

Donald R. Fisher, III
Project Manager
Roomstore Furniture Company
(804) 784-7600 extension 2124
DFisher@xxxxxxxxxxxxx

<clip>
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)
<clip>
--
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.