• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Scott Selikoff
Bartenders:
  • Piet Souris
  • Jj Roberts
  • fred rosenberger

Problem with SYS_CONNECT_BY_PATH

 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic