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
Received on Mon Mar 12 2007 - 22:45:25 CDT
