Home » SQL & PL/SQL » SQL & PL/SQL » Problem with SYS_CONNECT_BY_PATH (Oracle 9.2.0.7)
Problem with SYS_CONNECT_BY_PATH [message #306097] Wed, 12 March 2008 15:51 Go to next message
amber_vaidya
Messages: 4
Registered: March 2007
Junior Member
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 Sad.
 
 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
Re: Problem with SYS_CONNECT_BY_PATH [message #306109 is a reply to message #306097] Wed, 12 March 2008 18:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT customerid FROM targetlist
  2  /

CUSTOMERID
--------------------
1234
1234
2345
3456
4567
5678
6789

7 rows selected.

SCOTT@orcl_11g> SELECT * FROM account_manager
  2  /

CUSTOMERID           ACCTMGRID
-------------------- ------------------------------
1234                 9876
2345                 9876
2345                 6543
2345                 2342
3456                 6543
3456                 2342

6 rows selected.

SCOTT@orcl_11g> COLUMN acctmgrid FORMAT A30
SCOTT@orcl_11g> SELECT customerid,
  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (acctmgrid, ','), 2)) AS acctmgrid
  3  FROM   (SELECT DISTINCT t.customerid, am.acctmgrid,
  4  		    DENSE_RANK () OVER (PARTITION BY t.customerid ORDER BY am.acctmgrid DESC) AS seq
  5  	     FROM   targetlist t, account_manager am
  6  	     WHERE  t.customerid = am.customerid (+))
  7  START  WITH seq = 1
  8  CONNECT BY PRIOR seq + 1 = seq AND PRIOR customerid = customerid
  9  GROUP  BY customerid
 10  ORDER  BY customerid
 11  /

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

6 rows selected.

SCOTT@orcl_11g> 

Re: Problem with SYS_CONNECT_BY_PATH [message #306112 is a reply to message #306109] Wed, 12 March 2008 19:09 Go to previous message
amber_vaidya
Messages: 4
Registered: March 2007
Junior Member
Hi Barbara,
Thanks for the reply. Your query worked like a charm !!!!
I really appreciate it Smile

Warm Regards,
Amber
Previous Topic: CASE
Next Topic: How to store and retrieve audio files from the database
Goto Forum:
  


Current Time: Fri Dec 09 00:19:58 CST 2016

Total time taken to generate the page: 0.14826 seconds