[Logo]
Forums Register Login
Problem with SYS_CONNECT_BY_PATH
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
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.
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
Let's go to the waterfront with this tiny ad:
ScroogeXHTML 7.1 - RTF to HTML5 / XHTML converter
https://coderanch.com/t/690611/ScroogeXHTML-RTF-HTML-XHTML-converter


This thread has been viewed 2289 times.

All times above are in ranch (not your local) time.
The current ranch time is
Feb 23, 2018 05:38:54.