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." <[EMAIL PROTECTED]>
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
> >>[EMAIL PROTECTED]
> >>
> >>
> >>
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Joe Testa
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
