Re: Need connect by, sys_connect_by_path assistance

From: commandtek <CommandTek_at_gmail.com>
Date: 14 Mar 2007 12:11:56 -0700
Message-ID: <1173899516.132534.30200@l77g2000hsb.googlegroups.com>

On Mar 13, 11:00 am, DA Morgan <damor..._at_psoug.org> wrote:
> Command..._at_gmail.com wrote:
> > I'm a DBA on a DW project, taking care of my partitions, backups,
> > etc. One of our developers has come up with the following subselect
> > portion of a query that's giving us issues, and I'm not familiar
> > enough with use of connect by, sys_connect_by_path, etc. to rewrite
> > this thing, hoping someone can help. The query itself works fine
> > against the 130k table it references, it's when it gets wrapped into
> > other queries that we get errors like "ORA-01788: CONNECT BY clause
> > required in this query block". Any assistance would be greatly
> > appreciated.
>
> > select connect_by_root P.BUSINESS_ASSOCIATES_NAME as
> > root_vendor_name,
> > connect_by_root P.BUSINESS_ASSOCIATES_NUMBER as root_vendor_number,
> > connect_by_iscycle as IsCycle,
> > p.business_associates_number as Business_Associates_Number,
> > P.BUSINESS_ASSOCIATES_NAME as Business_Associates_Name,
> > decode(P.ASSOC_VENDOR_BA_NUMBER,null,'N/A',P.ASSOC_VENDOR_BA_NUMBER)
> > as Assoc_Vendor_Ba_Number,
> > decode(P.ASSOC_VENDOR_BA_NAME,null,'N/A',P.ASSOC_VENDOR_BA_NAME) as
> > Assoc_Vendor_Ba_Name,
> > level as TheLevel,
> > sys_connect_by_path(p.business_associates_number, '-' ) as path
> > from ACCTPAY_OWNER.DIM1_PAYEE P
> > connect by nocycle prior P.BUSINESS_ASSOCIATES_NUMBER =
> > P.ASSOC_VENDOR_BA_NUMBER
> > start with p.assoc_vendor_ba_number is null or
> > length(p.assoc_vendor_ba_number) = 0
> > or p.assoc_vendor_ba_number = p.BUSINESS_ASSOCIATES_number
>
> Seeing both the query that works and the query that doesn't would be
> helpful.
>
> PS: Does your Oracle installation have a version number?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Daniel,

Thanks much for the reply. Version is 10.2.0.2. Developers were able to get all their queries to work by modifying the previous version to this, notice the "and 1=1" in the "connect by nocycle" line:

select connect_by_root P.BUSINESS_ASSOCIATES_NAME as root_vendor_name,

connect_by_root P.BUSINESS_ASSOCIATES_NUMBER as  root_vendor_number,
p.business_associates_number as Business_Associates_Number,
P.BUSINESS_ASSOCIATES_NAME as Business_Associates_Name,

decode(P.ASSOC_VENDOR_BA_NUMBER,null,'N/A',P.ASSOC_VENDOR_BA_NUMBER) as Assoc_Vendor_Ba_Number,
decode(P.ASSOC_VENDOR_BA_NAME,null,'N/A',P.ASSOC_VENDOR_BA_NAME) as Assoc_Vendor_Ba_Name,
level as TheLevel,
sys_connect_by_path(p.business_associates_number, '-' ) as path from ACCTPAY_OWNER.DIM1_PAYEE P
connect by nocycle prior P.BUSINESS_ASSOCIATES_NUMBER = P.ASSOC_VENDOR_BA_NUMBER and 1=1
start with p.assoc_vendor_ba_number is null or p.assoc_vendor_ba_number = p.BUSINESS_ASSOCIATES_number

They also wanted to convert this query to a materialized view, but Oracle support has confirmed that Bug 537813 prevents this, and won't be fixed 'til version 11.

Thanks again,
Matt
commandtek_at_gmail_dot_com

PS: You guys have the best quick-reference Oracle site on the web, afaic. Received on Wed Mar 14 2007 - 14:11:56 CDT