Any chance its doing a INDEX DESC or INDEX MAX/MIN in
the plan? They're are two culprits I've seen in the
past that cause 'peculiar' result set to come back
hth
connor
- "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
wrote: > Accounts payable. But this is not a
Peoplesoft
> problem, but an Oracle one. I tried the same query
> against another Peoplesoft instance, and it ran
> fine. So there's something more than the view
> involved here. It's in Oracle Support's court now.
>
> I must be living right. It's failing in development
> and working in production.
>
> Ian
>
> -----Original Message-----
> Sent: Sunday, June 09, 2002 7:13 PM
> To: Multiple recipients of list ORACLE-L
>
>
> what modules, if you dont mind me asking, i'm at a
> site where we're
> going to implement HR, Financials And EPM soon.
>
> thanks, joe
>
>
> MacGregor, Ian A. wrote:
>
> >Yep sure is.
> >
> >Ian
> >
> >
> >-----Original Message-----
> >Sent: Sunday, June 09, 2002 5:43 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >Ian, is that peopleslop?
> >
> >joe
> >
> >
> >MacGregor, Ian A. wrote:
> >
> >>SQL> SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE
> ROWNUM < 10;
> >>
> >>VOUCHER_
> >>--------
> >>00003394
> >>00003395
> >>00003396
> >>00003397
> >>00003398
> >>00003399
> >>00003400
> >>00003401
> >>00003402
> >>
>
>>------------------------------------------------------------------------
> >>set feedback on
> >>
> >> 1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
> >> 2* WHERE VOUCHER_ID = '00003394'
> >>SQL> /
> >>VOUCHER_
> >>--------
> >>
> >>
> >>1 row selected.
>
>>----------------------------------------------------------------------------------
> >>Zounds !!! Select dump(voucher_id) shows that
> Oracle is returning a null here.
> >>
> >>Here a function is used to force the query to do
> full tablescans
> >>
> >>1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
> >> 2* WHERE CONCAT(VOUCHER_ID,'A') =
> CONCAT('00003394', 'A')
> >>SQL> /
> >>
> >>VOUCHER_
> >>--------
> >>00003394
>
>>-------------------------------------------------------------------------------------
> >>I get the expected results if I force full table
> scans.
> >>
> >>I looked at the explain plan for the failing query
> and rebuilt the ps_voucher index. This
> >>did not change its erroneous results. I rebuilt
> the view itself again to no avail. A check on
> Metalink revealed bug 1852163. Although this bug's
> conditions were different from the one above, their
> were some similarities. One workaround for bug
> 1852163
> >>
> >>alter session set "_complex_view_merging" = true;
> >>
> >>I tried this and the original query still gave
> improper results.
>
>>-----------------------------------------------------------------------------------------------
> >>All queries against the component tables of the
> view work fine.
>
>>------------------------------------------------------------------------------------------
> >>The view text is
> >>
> >>CREATE VIEW SYSADM.PS_VCHR_MM_VW
> >>AS
> >>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID,
> A.INVOICE_ID,
> >>A.INVOICE_DT, A.PROCESS_INSTANCE,
> A.ENTRY_STATUS, A.POST_STATUS_AP,
> >>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG,
> A.ORIGIN FROM
> >>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C
> WHERE A.BUSINESS_UNIT =
> >>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID =
> C.VOUCHER_ID AND
> >>A.MATCH_ACTION IN ('Y', 'E')
>
>>-------------------------------------------------------------------------------------
> >>If I run the select statement outside of the view
> and tack on the 'voucher_id = ' clause
> >>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID,
> A.INVOICE_ID,
> >>A.INVOICE_DT, A.PROCESS_INSTANCE,
> A.ENTRY_STATUS, A.POST_STATUS_AP,
> >>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG,
> A.ORIGIN FROM
> >>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C
> WHERE A.BUSINESS_UNIT =
> >>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID =
> C.VOUCHER_ID AND
> >>A.MATCH_ACTION IN ('Y', 'E')
> >>and a.voucher_id = '00003394'
> >>/
> >>
> >>I get the expected results. The query plan
> matches the one for the failing statement.
>
>>----------------------------------------------------------------------------------------------
> >>If I select more than voucher_id from the view
> with the 'voucher_id = ' predicate
> >>the other fields are projected correctly, but
> returns voucher_id as null.
>
>>===========================================================================================
> >>
> >>Ian MacGregor
> >>Stanford Linear Accelerator Center
> >>ian_at_slac.stanford.edu
> >>
> >>
> >>
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Joe Testa
> INET: jtesta_at_dmc-it.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Jun 10 2002 - 06:18:22 CDT