RE: SQL question

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

One of the replies used the DIGITS() function and still ran a table 
scan. This illustrates a basic rule - avoid expressions that use 
functions in the WHERE clause and in JOIN specifications, because it 
will ALWAYS result in a table scan (or probably at least 99% of the 
time). This is because it cannot eliminate anything without first 
running 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 it 
has 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.