ORACLE: sub-query into delimited list?

[WolfShade]

Hello, everyone.

ORACLE 11.2

I'm working on a project that has several related tables, and is currently using a large query with a LOT of LEFT OUTER JOINs to grab the data. I'm getting most of the stuff I need, but I have one roadblock that I can't seem to get around.

There can be more than one developer associated with a job. How can I convert (arbitrary number) 3 developers into a comma-delimited list and keep it in one column of a record?

I've tried wm_concat() with zero success.

Thank you,

[Fou-Lu]

Don't. Collections of data should never go into a single record property. Use a flattening table to create a many to many (assuming that one developer can be associated with more than one job as well of course).

[WolfShade]

Yes, one developer can work on more than one job at a time.

I've never heard of "flattening table". What does this entail?

Thank you,

[Fou-Lu]

+--------------+             +-----------------------+           +----------------+
| Developer    |             | ProjectDeveloper      |           | Project        |
+--------------+             +-----------------------+           +----------------+
| devid [PK]   |>+---------o<| projectid [PK][FK]    |>o-------+<| projectid [PK] |
+--------------+             | devid [PK][FK]        |           +----------------+
                             +-----------------------+

Simple as that. Associate two objects together in a manner which one relates directly to the other to create a valid tuple, and you have a many to many relation. Now you can join both project and developer against the projectdeveloper to get information from either or both.

[WolfShade]

Ah.. okay.. I'm not the one who designed the database, and the one who did is already implementing that structure. So.. I'm already getting two records per job because the status history has two entries (open and working; a third would bring up three.) This will currently expand to four records if I have two developers on a job.

I can't copy/paste the query, here (dev system is isolated from internet), and it's too much to manually re-type the whole thing, so I can't really give an example of what I currently have. But I currently have 8 left outer joins going. I wanted to try to get this all in one query so I'm not making ten connections to the database. But if this is more complex, perhaps that would be a better idea?

Thank you for your input, I appreciate it.

[Fou-Lu]

Which design are you talking about; the one I have for the many to many, or the one with a single property containing a comma delimited list?
I'd have to dust off the old oracle book to even start determining what you would need to do with the comma delimited list, and then install an oracle to test it (no way work would let me build a new db just to test). I'm fairly good with structural design, but my query skills are somewhat mediocre.
Problem is that's simply a string. I don't think it will work directly with a correlated subquery, but I guess you could try that to see (if you want; correlated are IMO too slow). You wouldn't be able to use it as a part of a join though since it is literally just a string.

To me it sounds like you have it built correctly based on what you have said for record counts to data. This is normal, for every entry matching within the query, you have replication for the other controlling fields. So if you looked for projectid, projectname, and developername based on the 3 tables above, and you have 2x developers, you would have two records both of which contain projectid and projectname as well as the developername. You then use the language to format it appropriately for display.

[WolfShade]

Yes, the design you outlined is how it's currently developed for the many-to-many.

I know I could use the language to format it, I was just hoping that I'd be able to make this a little more efficient by returning one record instead of many. Don't get me wrong, our network isn't that bottlenecked.. I'm just neurotic and a little OCD about keeping bandwidth and CPU to minimum.

Thank you for your advice.

[Fou-Lu]

I can't be certain, but I would almost anticipate that any approach using the ws_concat (or writing a custom aggregate to deal with it), would actually prove to be more resource intensive than the resultset with duplication per row.

So yeah, I mean you should be able to pull the results in a single returned property (so you can get John, Gerry, Mellisa for examples), but I'm not sure if it would be faster than pulling up the duplicates. Just thinking of the sizes, if I had a description and a developer name both 20chars in length for a field specification, then if I where to pull three entries I'd end up with (assuming Char and not a varchar type) 40x3 bytes for 120bytes total give or take. On the other hand, grouping them would result in at least 20 + 20x3, or 80bytes. I don't know what the ws_concat does in regards to this; I would assume that it would take the maximum size of the field specified and multiplies it by the known number of results. Although it will waste more cycles, so. . .
Maybe I should pose the question to my oracle datalord tomorrow to see what he says.

[WolfShade]

Much appreciated, Fou-Lu.

[Fou-Lu]

I talked the oracle datalord this morning, and here's how he pretty much described it.
If you are using a VARCHAR field, then the use of ws_concat will result in the size that it has available at max.
So if you have projectname and devname, both 20 chars max for VARCHAR and assigned three devs to the project "Project1": Mary, John, Joe, this would result in 8*3 + 4 + 4 + 3 for a total of 35bytes (project name * records + each developer name). However, using ws_concat would result in 8 + 20 * 3 for a total of 68bytes (project name + 3 devs at a total of 20bytes in the varchar). This assumes a single byte charset of course. Also ignoring any standard overhead of the datatypes in question.
Now he said if you use a VARCHAR2 datatype, the ws_concat would be the sizeof the actual used chars + delimiters. So ultimately, if you use the VARCHAR type, you should make use of the multiple records instead. If you use VARCHAR2, you could use the ws_concat to group the results in the SQL.

[WolfShade]

Awesome. Thank you! But I never could get the wm_concat() to work, and I think the fields are VARCHAR2.

Yup.. they are VARCHAR2.

But I keep getting different errors on the wm_concat(), either "not a single-group grouip command" or ... I forget the other one. ::sheepish grin::

[Fou-Lu]

Just like MySQL you need to group by in order to use the wm_concat or listagg.
So for an example with just the intermediate table:

SELECT p.projectid, WM_CONCAT(d.devname)
FROM ProjectDeveloper pd
INNER JOIN Developer d ON d.devid = pd.devid
INNER JOIN Project p ON p.projectid = pd.projectid
WHERE p.projectid = {APROJECTID}
GROUP BY p.projectid

I think something like that would work.
I really should go through my books again anyway; I've forgotten how interesting oracle is.

[WolfShade]

"Interesting".. yeah, I guess that's one way to put it. I've only just recently started learning Oracle, and I prefer (gasp) MS-SQL.

So doing a GROUP BY on the table that is NOT within the wm_concat() should help eliminate (or, at the very least, give a different) error message?

EDIT: When using anything for GROUP BY, I get either "not a GROUP BY expression" or "not a single-group group expression".

Thank you,

[Fou-Lu]

Originally Posted by WolfShade

"Interesting".. yeah, I guess that's one way to put it. I've only just recently started learning Oracle, and I prefer (gasp) MS-SQL.

So doing a GROUP BY on the table that is NOT within the wm_concat() should help eliminate (or, at the very least, give a different) error message?

EDIT: When using anything for GROUP BY, I get either "not a GROUP BY expression" or "not a single-group group expression".

Thank you,

Let me see if I can do anything with my little Oracle (its an express; I'm not sure the version offhand) that can mimic what you are doing here.

Edit:
Yeah I can't compare; I have a 10g express, so ws_concat and listagg won't be available.
You are trying that just between three tables yeah? If you select more than one field you will need to add it as a part of the group by, or use a subquery (which will be slow).

[WolfShade]

This is a very truncated version of what I am trying to do:

SELECT t1.JID, t1.JDESC, t1.CreateDate, t1.JSTATUS, t1.LastMod, 
    wm_concat(t7.JSTATUS_USERID) JSTATUS_USERID, 
    wm_concat(t7.JSTATUS_HISTORY) JSTATUS_HISTORY, 
    wm_concat(t7.EnteredDate) EnteredDate, t5.APP_VER, t6.APP_MFG

FROM table1 t1
    LEFT OUTER JOIN table2 t2 ON t2.JID = t1.JID
    LEFT OUTER JOIN table3 t3 ON t3.WID = t2.WID
    LEFT OUTER JOIN table4 t4 ON t4.UID = t1.UID
    LEFT OUTER JOIN table5 t5 ON t5.SID = t4.SID
    LEFT OUTER JOIN table6 t6 ON t6.MID = t5.MID
    LEFT OUTER JOIN table7 t7 ON t7.SNID = t5.SNID
    LEFT OUTER JOIN table8 t8 ON t8.JID = t1.JID
    LEFT OUTER JOIN table9 t9 ON t9.JID = t1.JID AND t9.UTYPE = 'C'

WHERE t1.JID = ' one long a$$ string '

Thank you, again.