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



Ok, I also found this in the indexing strategies whitepaper:
http://www-03.ibm.com/servers/enable/site/bi/strategy/strategy.pdf

"Avoid using derived expressions in a local selection or join condition. Access
via an index may not be used for predicates that have derived values. Or, a
temporary index will be created to provide key values and attributes that
match the derivative. For example, if a query includes one of the following
predicates:
WHERE SHIPDATE > (current_date - 10) or
UPPER(customer_name) = ‘SMITH’
...the optimizer considers that predicate to be a derived value and may not
use an index for local selection."

HTH,
Charles



On Wed, Feb 17, 2010 at 9:53 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi Charles
***NOTE** This first piece was added last as I read my original e-mail and
realized that my question could come off as we being condescending
BUT this is a valid question.

nice answer, but I have a question
How do you know that "you will force the system to do at minimum a full
index scan and at maximum a full table scan"
will be done? Where can I find such documentation?
Thanks in advance


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



            Charles Wilt
            <charles.wilt@gma
            il.com>                                                    To
            Sent by:                  Midrange Systems Technical
            midrange-l-bounce         Discussion
            s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>
                                                                       cc

            02/17/2010 09:42                                      Subject
            AM                        Re: SQL Question


            Please respond to
            Midrange Systems
                Technical
               Discussion
            <midrange-l@midra
                nge.com>






Note however that by using scalar functions (substring and concat) on
FLD6 you will force the system to do at minimum a full index scan and
at maximum a full table scan unless you're at 6.1 and have a derived
index.

A better idea, since you're only looking for =, is to simply reformat
the just value you've got for SYSDATE.

SELECT FLD1, FLD2, FLD3, FLD4
FROM MYFILE
WHERE FLD6  = (SELECT dec(substr(SYSDATE,7,2)
                                               concat  substr(SYSDATE,5,2)
                                               concat  substr(SYSDATE,3,2)
                                              ,6,0)
                             FROM SYSPARM WHERE ENV= "PRD")

Now if you've got an index on FLD6, the system will be able to do an
index seek to find the rows.

HTH,
Charles Wilt



On Wed, Feb 17, 2010 at 9:15 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi Dina
here is one suggestion
FLD6 is a decimal so you can convert that into character by the use of
DIGITS(FLD6)
This can then be parsed out (love that word)  - I am assuming the date in
FLD6 is of the format ddmmyy
SUBSTR(DIGITS(FLD6), 5, 2) || SUBSTR(DIGITS(FLD6), 3, 2) || SUBSTR(DIGITS
(FLD6), 1, 2)
where || is concatenation (I forget the word to use)

This can then be compared to the last six characters of SYSDATE
SELECT FLD1, FLD2, FLD3, FLD4

FROM MYFILE

WHERE
SUBSTR(DIGITS(FLD6), 5, 2) || SUBSTR(DIGITS(FLD6), 3, 2) || SUBSTR(DIGITS
(FLD6), 1, 2)  =
SELECT SUBSTR(SYSDATE, 3, 6) FROM SYSPARM WHERE ENV= "PRD")



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



            dina ramzy
            <dina.ramzy@live.
            com>                                                       To
            Sent by:                  <midrange-l@xxxxxxxxxxxx>
            midrange-l-bounce                                          cc
            s@xxxxxxxxxxxx
                                                                  Subject
                                      SQL Question
            02/17/2010 09:08
            AM


            Please respond to
            Midrange Systems
                Technical
               Discussion
            <midrange-l@midra
                nge.com>








Hi All,



Could you please advise me with this query:



SELECT FLD1, FLD2, FLD3, FLD4

FROM MYFILE

WHERE FLD6  = (SELECT SYSDATE FROM SYSPARM WHERE ENV= "PRD")



The problem is FLD6 is decimal of 6 containing data like 160210

and SYSDTAE is char of 8 containting date like 20100216. and I can not
make
the comparison...







Thanks in advance for your help



Dina

_________________________________________________________________
Hotmail: Powerful Free email with security by Microsoft.
https://signup.live.com/signup.aspx?id=60969
--
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.


Hi All,



Could you please advise me with this query:



SELECT FLD1, FLD2, FLD3, FLD4

FROM MYFILE

WHERE FLD6  = (SELECT SYSDATE FROM SYSPARM WHERE ENV= "PRD")



The problem is FLD6 is decimal of 6 containing data like 160210

and SYSDTAE is char of 8 containting date like 20100216. and I can not
make
the comparison...







Thanks in advance for your help



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


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

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.