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 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    I've tried adding every field to the GROUP BY. Part of the problem is that some of the column names are being aliased (I've got a CASE/WHEN in place because this is pulling data for one of three types of jobs: Application, Software, or Server. The CASE/WHENs both end with the alias name.)
    ^_^

    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. #17
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Try adding group by for each field you have in order of control.
    I'm not sure how that will work at all; I've never used the concat functionality in oracle, and I've avoided it in MySQL in favor of object creation, let alone over many different tables joined in. I can't see the number of joins actually being relevant though, you should be able to group it on whatever you want from the selections.

    Edit:
    Use the original names, not the aliased names in a group by clause.

  3. #18
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    Quote Originally Posted by Fou-Lu View Post
    Edit:
    Use the original names, not the aliased names in a group by clause.
    Then I'll need to stop trying. The CASE/WHENs are looking at one of three different tables, and they don't all have the same column names where the aliased info is being pulled from. One table might have SOFTWARE_ID, another APPLICATION_ID, the other SERVER_ID, then aliasing that as ITEM_ID.

    Thank you, so much, for your help. I'll just go with the multiple connections route.
    ^_^

    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. #19
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Can those tables be unioned in some fashion?
    Give it a nested query instead. Or just don't use the ws_concat :]

    Edit:
    BTW if it helps here's an example of one that I have in MySQL:
    Code:
    SELECT u.name AS usergroupname, GROUP_CONCAT(p.name ORDER BY p.name ASC SEPARATOR ', ') AS members
    FROM acl_usergroupmembers ugm
    INNER JOIN core_principal u ON u.pid = ugm.usergroupid
    INNER JOIN core_principal p ON p.pid = ugm.pid
    GROUP BY u.name
    ORDER BY usergroupname ASC
    Which returns me:
    Code:
    *************************** 1. row ***************************
    usergroupname: Administrators
          members: Administrator, Fou-Lu
    *************************** 2. row ***************************
    usergroupname: Users
          members: Administrators, Fou-Lu
    Which (despite how it may look) is accurate.
    You'll noticed that MySQL will let me use a group by on an alias. I don't believe that Oracle does simply because of how the engines differ in their approaches. Oracle will have a way though for sure, I just don't know what it is.
    Last edited by Fou-Lu; 12-08-2012 at 04:46 PM.

  5. #20
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    EDIT: Nevermind.. I keep forgetting that CF Builder (for some messed up reason) doesn't display commas in DSN query output. CFDUMP shows that the comma delimiters are, indeed, in place. This question can be ignored.

    Thank you.


    Okay.. working on a different project, now, and I didn't get WM_CONCAT to work on the old project.. BUT, I did finally get WM_CONCAT to work on this current project - with one minor issue: it's not a delimited list. I was hoping for "valA,valB,valC", and I'm getting "valAvalBvalC".

    How can I get some kind of delimiter sandwiched in between the values?

    Thank you,
    Last edited by WolfShade; 01-18-2013 at 08:01 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 2 of 2 FirstFirst 12

Posting Permissions

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