Question around SYS_CONNECT_BY_PATH

[amber]

I have 2 tables, TARGETLIST and ACCOUNT_MANAGER as follows

TARGETLIST
 Name                Type
 ------------------- ----------------------------

 CUSTOMERID          VARCHAR2(20)
 TREATMENTCODE       NUMBER(19)
 


ACCOUNT_MANAGER
 Name                Type
 ------------------  ----------------------------
 
 CUSTOMERID          VARCHAR2(20)
 ACCTMGRID           VARCHAR2(20)

For all the CUSTOMERIDs in TARGETLIST, I am trying to find out the ACCTMGRIDs from ACCOUNT_MANAGER in a comma separted string

The data conditions are

1. There can be one or more ACCTMGRIDs per CUSTOMERID in the ACCOUNT_MANAGER table.
2. Not all the CUSTOMERIDs from TARGETLIST will be in the ACCOUNT_MANAGER table.
3. The TARGETLIST table may have multiple rows with the same CUSTOMERID

So, the final result should ideally look like this:

 CUSTOMERID		ACCTMGRID
 -------------------------------------------
 1234			9876
 2345			9876,6543,2342
 3456			6543,2342
 4567
 5678
 6789

Here's the query I came up with, but its not working :(.

 
 select CUSTOMERID, substr(SYS_CONNECT_BY_PATH(ACCTMGRID, ','),2) AMID  
 from ( 
    select 
 	DISTINCT(T.CUSTOMERID) CUSTOMERID ,AM.ACCTMGRID ACCTMGRID,
      count(*) OVER ( partition by T.CUSTOMERID ) cnt,
      ROW_NUMBER () OVER ( partition by T.CUSTOMERID order by AM.ACCTMGRID) seq 
      FROM TARGETLIST T, ACCOUNT_MANAGER AM  
    where  
      T.CUSTOMERID = AM.CUSTOMERID (+) 
   )
 where 
    seq=cnt start with seq=1 connect by prior seq+1=seq and prior CUSTOMERID=CUSTOMERID

It gives out a result like

 CUSTOMERID		ACCTMGRID
 -------------------------------------------
 1234			9876,9876,9876 - Once for each row of CUSTOMERID in
                                             the TARGETLIST table
 2345			9876,6543,2342 
 3456			6543,2342
 4567			,,,,
 5678			,, - When TARGETLIST table has 3 rows for the 
                                CUSTOMERID 5678	
 6789			,  - When TARGETLIST table has 2 rows for the 
                                  CUSTOMERID 6789

Can someone please help me?

Thanks in advance

[tamilselvan]

Can you define PK for the 2 tables?