Re: SQL question

On 10/29/05, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
> 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.

that is an interesting point Vern.  Is this true on all platform
implementations of SQL? What about those "deterministic" attributes of
the sql function, do they enable SQL to reuse and cache function
results?

I am curious about this because the large i5 where I am at is being
brought to its knees by sql coded by people who dont have an as400
background.

-Steve

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