• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with SYS_CONNECT_BY_PATH

 
Amber Vaidya
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have 2 tables, TARGETLIST and ACCOUNT_MANAGER as follows


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:


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


It gives out a result like


Can someone please help me?

Thanks in advance
 
Gayathri Chowduru
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic