Is there some way to modify the subqueries to that it will concatenate the rows when it returns multiple records or will I need to create a function that does this for me? I was trying to avoid creating a function as we would need to call it about 20 times per record. Thanks for your help.
SELECT . . . . select ias.status_change_date from per_assignment_status_types past, irc_assignment_statuses ias where past.assignment_status_type_id = ias.assignment_status_type_id and ias.assignment_id = asg.assignment_id and past.user_status = 'Resume Reviewed' and rownum = 1 FROM . . WHERE . .
Frank Kulash wrote:Thank you for your explanation. I will check out the Ask Tom page when available (getting a server down error) and will research the others that you mentioned. Thanks again.
That's called "String Aggregation"
[AskTom.oracle.com|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402] shows several different ways to do it.
The first way on that page, a user-defined agggregate function called STRAGG, is probably the easiest to use. You have to install the function (copy about 60 lines of code from that page and run it) once. After it's installed, you can use it in as many different queries as you want.
On Oracle 10 (and up) you may have a similar function, WM_CONCAT (owned by WMSYS), already installed.
WM_CONCAT is not documented, so you may not want to use it in your Production applications.
STRAGG is not so convenient if the order of items in the concatenated string is important.
In that case, use XMLAGG or SYS_CONNECT_BY_PATH, as shown later in the asktom page.
MODEL can also do ordered string aggregation.
Again, this is just a subquery in my main cursor. The problem with this is that it does not like the line indicated. ASG table is in the main query and I cannot reference it in this subquery (not sure why). Can someone tell me the correct syntax to integrate my query? Sorry, this one is a little over my head. Thanks.
(SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ias.status_change_date,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') FROM (SELECT ias.assignment_id, ias.status_change_date, ROW_NUMBER() OVER (PARTITION BY ias.assignment_id ORDER BY ias.status_change_date) AS curr, ROW_NUMBER() OVER (PARTITION BY ias.assignment_id ORDER BY ias.status_change_date) -1 AS prev FROM per_assignment_status_types past, irc_assignment_statuses ias where past.assignment_status_type_id = ias.assignment_status_type_id and ias.assignment_id = asg.assignment_id--------------------------------------------------------problem here and past.user_status = 'Active Application') GROUP BY ias.assignment_id CONNECT BY prev = PRIOR curr AND ias.assignment_id = PRIOR ias.assignment_id START WITH curr = 1)