Hi,
Could someone please provide a solution to the following query? Currently, I'm receiving an "Invalid Identifier" error. Thank you!
a. For the insert query, I am using the 'START WITH' clause. Here, I am joining the 'AIP.IP_ID' condition from outer query, but it is not working. Please check and share your thoughts.
SELECT
(
SELECT LISTAGG(aff_name, ', ') WITHIN GROUP(ORDER BY aff_name) Names
FROM rep_affiliates
WHERE AFF_NUM IN
(
SELECT distinct SUBSTR(VALUE,1,INSTR(VALUE,'-',1)-1) ip_number
FROM
(
SELECT
TRIM(REGEXP_SUBSTR(VALUE,'[^/]+', 1, LEVEL) ) VALUE
FROM ( select
HEIR_PATH VALUE --/413227-1.000000/410635-1.000000/41048-1.000000 (The value is coming in this way, which is why I added the "CONNECT BY" clause.)
FROM prop_collector_agreements pca1
WHERE 1=1
AND COLLECTS_ON_AGREEMENT_ID is not null
AND END_DATE IS NULL
AND UPPER(company) IN('SESAC','HFAMLC')
START WITH (collector_id = aip.ip_id AND collects_on_agreement_id IS NULL)--this part getting error
CONNECT BY collects_on_agreement_id = PRIOR collector_agreement_id)
connect BY REGEXP_SUBSTR(VALUE, '[^/]+', 1, LEVEL) IS NOT NULL
))) Names
FROM aff_membership_agreements ama ,
aff_interested_parties aip ,
rep_affiliates ra ,
aff_ip_names ain ,
aff_earnings_rank aer ,
REP_AFF_ADDRESSES_V adr
WHERE TRUNC(ama.valid_to_date) BETWEEN ADD_MONTHS(TRUNC(SYSDATE),3) AND ADD_MONTHS(TRUNC(SYSDATE),6)
AND ama.ip_id = aip.ip_id
AND ain.ip_id = ama.ip_id
AND ama.society_id = 72
AND ama.status = 'A'
AND ain.name_type_id = 6
AND ra.ip_id = aip.ip_id
AND aer.affiliate_number(+) = aip.ip_number
AND aip.ip_id = adr.ip_id(+)
AND address_type(+) = 'MAILING';
Thank you in advance.