Problem with SYS_CONNECT_BY_PATH

[Amber Vaidya]

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:

 CUSTOMERIDACCTMGRID
 -------------------------------------------
 12349876
 23459876,6543,2342
 34566543,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

 CUSTOMERIDACCTMGRID
 -------------------------------------------
 12349876,9876,9876 - Once for each row of CUSTOMERID in
                                             the TARGETLIST table
 23459876,6543,2342 
 34566543,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

[Gayathri Chowduru]

Hi Amber,

I think there is no need to write that much complex query

Try with the below query,

SELECT t.customerid, a.acctmgrid FROM tagrgetlist t, account_manager a
WHERE t.customerid = a.customerid(+) group by t.customerid;

I guess it works. Please let me know if you get the required output.

Thanks,
Gayathri.

[Amber Vaidya]

Nope.. That does not work because I need the ACCTMGRIDs in a comma separated list on one row.

Here's the one that finally worked

   
SELECT customerid,
        MAX (SUBSTR (SYS_CONNECT_BY_PATH (acctmgrid, ','), 2)) AS acctmgrid
    FROM   (SELECT DISTINCT t.customerid, am.acctmgrid,
        DENSE_RANK () OVER (PARTITION BY t.customerid ORDER BY am.acctmgrid DESC) AS seq
         FROM   targetlist t, account_manager am
         WHERE  t.customerid = am.customerid (+))
    START  WITH seq = 1
    CONNECT BY PRIOR seq + 1 = seq AND PRIOR customerid = customerid
    GROUP  BY customerid
   ORDER  BY customerid