Participating Frequently

Question

cfquery output dilemma

Forum|Forum|15 years ago
August 27, 2010
3 replies
964 views

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%'

This topic has been closed for replies.

BKBK

Community Expert

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

A

Anonymous

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

A

Anonymous

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

M

mostlyconfusedAuthor

Participating Frequently

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.

D

Dan_Bracuk

Inspiring

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

D

Dan_Bracuk

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded