ORACLE: sub-query into delimited list?

[WolfShade]

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.)

[Fou-Lu]

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.

[WolfShade]

Originally Posted by Fou-Lu

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.

[Fou-Lu]

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:

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:

*************************** 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.

[WolfShade]

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,