Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts

    Question ORACLE: sub-query into delimited list?

    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,
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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).

  • #3
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    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,
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Code:
    +--------------+             +-----------------------+           +----------------+
    | 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.

  • #5
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    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.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #7
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    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.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #9
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    Much appreciated, Fou-Lu.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #11
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    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::
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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:
    Code:
    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.

  • #13
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    "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,
    Last edited by WolfShade; 12-07-2012 at 07:58 PM.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by WolfShade View Post
    "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).
    Last edited by Fou-Lu; 12-07-2012 at 08:14 PM.

  • #15
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    This is a very truncated version of what I am trying to do:

    Code:
    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.
    Last edited by WolfShade; 12-07-2012 at 08:25 PM.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •