Re: Need connect by, sys_connect_by_path assistance

CommandTek_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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org