cfquery output dilemma

[mostlyconfused]

I'm trying to determine if what I need is possible through query of queries or any other function in CF. We are currently using CF version 8.

I cannot determine if there is a way to merge query results to a single row of output rather than multiple rows....

I'm pulling results from multiple tables with inner and left outer joins.
Currently, I get the system id, product version and contract information from the query. For simplicity let's say the result is this:

503220   7.0.2CM04     Active

If I include the last required field from another table (team_BUS_ORG.NAME), the results triple because there can be multiple values in that table/field for each system id, similar to this:

503220     7.0.2CM04     Active     T01- Cust Support
503220     7.0.2CM04     Active     Field Support-East
503220     7.0.2CM04     Active     Acme widgets

The problem is the user requirement is to show one single record and include the 'Cust Support' entry if it exists, but trying to include "where team_BUS_ORG.NAME like '%Cust Support'" in the SQL statement as criteria will eliminate any record that doesn't have a '%Cust Support' match from the result set. I'm trying to figure out how to use query of queries or any other CF function to give me one returned record per system id, even if there is no 'Cust Support' entry, but include the 'Cust Support' entry in the result set if it exists.

Is this possible?

The query, if it's of any help, would look something like this if I were to include the business org field in the query (I've eliminated a lot of other fields and joins for the purposes of this discussion, so forgive me if there is an sql error in it)...

SELECT
sys_SITE_PART.SERIAL_NO AS SYSTEMID,
sys_MOD_LEVEL.MOD_LEVEL AS RELEASE,
sys_CONTRACT.STATUS AS CONTRACTSTATUS,
REPLACE(team_BUS_ORG.NAME, ' - Product Support Team',') AS TEAM_NAME

FROM   (((((SA.TABLE_SITE_PART sys_SITE_PART
INNER JOIN SA.TABLE_MOD_LEVEL sys_MOD_LEVEL ON sys_SITE_PART.SITE_PART2PART_INFO=sys_MOD_LEVEL.OBJID)
INNER JOIN SA.MTM_SITE_PART24_CONTRACT5 sys_SITE_PART24_CONTRACT5 ON sys_SITE_PART.OBJID=sys_SITE_PART24_CONTRACT5.DIR_SITEPART2CONTRACT)
INNER JOIN SA.TABLE_CONTRACT sys_CONTRACT ON sys_SITE_PART24_CONTRACT5.CONTRACT2DIR_SITEPART=sys_CONTRACT.OBJID)
INNER JOIN SA.TABLE_PART_NUM sys_PART_NUM ON sys_MOD_LEVEL.PART_INFO2PART_NUM=sys_PART_NUM.OBJID)
LEFT OUTER JOIN SA.MTM_SITE_PART63_BUS_ORG108 ON sys_SITE_PART.OBJID=SA.MTM_SITE_PART63_BUS_ORG108.system_pto2bus_org)
LEFT OUTER JOIN SA.TABLE_BUS_ORG team_BUS_ORG ON mtm_site_part63_bus_org108.bus_org2system_pto=team_BUS_ORG.OBJID

WHERE (sys_SITE.SITE_ID = sys_SITE.SITE_ID) and team_BUS_ORG.NAME like '%Product Support%'

[Dan_Bracuk]

Something like this:

Make your query something like this:

select id, field1, 'really long string' concatfield

Then loop through it and use QuerySetCell to change the value of your concatfield.

There will be some if/else logic on the id field to see if it's the same as the last one of course.

[Guest]

Unless I have misunderstood your requirements, you should be able to do this by modifying your query just alittle:

SELECT
sys_SITE_PART.SERIAL_NO AS SYSTEMID,
sys_MOD_LEVEL.MOD_LEVEL AS RELEASE,
sys_CONTRACT.STATUS AS CONTRACTSTATUS,
REPLACE(MAX(team_BUS_ORG.NAME), ' - Product Support Team',') AS TEAM_NAME

FROM   (((((SA.TABLE_SITE_PART sys_SITE_PART
INNER JOIN SA.TABLE_MOD_LEVEL sys_MOD_LEVEL ON sys_SITE_PART.SITE_PART2PART_INFO=sys_MOD_LEVEL.OBJID)
INNER JOIN SA.MTM_SITE_PART24_CONTRACT5 sys_SITE_PART24_CONTRACT5 ON sys_SITE_PART.OBJID=sys_SITE_PART24_CONTRACT5.DIR_SITEPART2CONTRACT)
INNER JOIN SA.TABLE_CONTRACT sys_CONTRACT ON sys_SITE_PART24_CONTRACT5.CONTRACT2DIR_SITEPART=sys_CONTRACT.OBJID)
INNER JOIN SA.TABLE_PART_NUM sys_PART_NUM ON sys_MOD_LEVEL.PART_INFO2PART_NUM=sys_PART_NUM.OBJID)
LEFT OUTER JOIN SA.MTM_SITE_PART63_BUS_ORG108 ON sys_SITE_PART.OBJID=SA.MTM_SITE_PART63_BUS_ORG108.system_pto2bus_org)
LEFT OUTER JOIN SA.TABLE_BUS_ORG team_BUS_ORG ON mtm_site_part63_bus_org108.bus_org2system_pto=team_BUS_ORG.OBJID

WHERE (sys_SITE.SITE_ID = sys_SITE.SITE_ID) and team_BUS_ORG.NAME like '%Product Support%'

GROUP BY sys_SITE_PART.SERIAL_NO, sys_MOD_LEVEL.MOD_LEVEL, sys_CONTRACT.STATUS

Just to explain the changes (in blue):

• The group by is to remove duplicates you mentioned, in conjuction with:
• an aggregate on team_bus_org.name, you can use MAX or MIN here since it will always be the same because of your WHERE clause.

I don't quite understand the part in magenta, as these values will always be equal.

Hope this helps

Cheers

[mostlyconfused]

My apologies if I was unclear. The query was cited as what would be used if I could keep the 'team_BUS_ORG.NAME like '%Product Support%'' in the WHERE clause. It was more of a reference point than anything else. But I can't use that in the WHERE clause because it will eliminate any record that doesn't have a '%Product Support' entry from the return results. I'm looking into the QUERYSETCELL and cfloop possibility as previously suggested.

Thank you for the reply.

[Dan_Bracuk]

Yeah, about that suggestion, upon further review, QueryNew() and QueryAddRow() will probably be required.

[Guest]

I see what you mean, then a nested query will surely do the trick.  Doing as much data operations on the database side as possible will also be more effcient.

Some thing like:

SELECT
sys_SITE_PART.SERIAL_NO AS SYSTEMID,
sys_MOD_LEVEL.MOD_LEVEL AS RELEASE,
sys_CONTRACT.STATUS AS CONTRACTSTATUS,
REPLACE( MAX( ISNULL(team_BUS_ORG.NAME, '') ), ' - Product Support Team','') AS TEAM_NAME

FROM   (((((SA.TABLE_SITE_PART sys_SITE_PART
INNER JOIN SA.TABLE_MOD_LEVEL sys_MOD_LEVEL ON sys_SITE_PART.SITE_PART2PART_INFO=sys_MOD_LEVEL.OBJID)
INNER  JOIN SA.MTM_SITE_PART24_CONTRACT5 sys_SITE_PART24_CONTRACT5 ON  sys_SITE_PART.OBJID=sys_SITE_PART24_CONTRACT5.DIR_SITEPART2CONTRACT)
INNER JOIN SA.TABLE_CONTRACT sys_CONTRACT ON sys_SITE_PART24_CONTRACT5.CONTRACT2DIR_SITEPART=sys_CONTRACT.OBJID)
INNER JOIN SA.TABLE_PART_NUM sys_PART_NUM ON sys_MOD_LEVEL.PART_INFO2PART_NUM=sys_PART_NUM.OBJID)
LEFT OUTER JOIN SA.MTM_SITE_PART63_BUS_ORG108 ON sys_SITE_PART.OBJID=SA.MTM_SITE_PART63_BUS_ORG108.system_pto2bus_org)
LEFT OUTER JOIN

     (

          SELECT OBJID, NAME FROM SA.TABLE_BUS_ORG WHERE NAME like '%Product Support%'

     )

team_BUS_ORG ON mtm_site_part63_bus_org108.bus_org2system_pto=team_BUS_ORG.OBJID

WHERE (sys_SITE.SITE_ID = sys_SITE.SITE_ID)

GROUP BY sys_SITE_PART.SERIAL_NO, sys_MOD_LEVEL.MOD_LEVEL, sys_CONTRACT.STATUS

Is this more of what you were looking for?

Cheers

[BKBK]

You've already done half the work! I was thinking of something like

select etc., etc.
from tbl
where team_BUS_ORG.NAME like '%Cust Support'
group by system_id

union

select etc., etc.
from tbl
where team_BUS_ORG.NAME not like '%Cust Support'
group by system_id

[Guest]

but since your only grouping on a system_id there, that would be the same as?:

select etc., etc.
from tbl
group by system_id