Hi,
I'm stumped on how to match data from 2 queries. What is the best way?
Here's the situation....
Query A
--------------
<CFQUERY NAME="qSSR" DATASOURCE="#DSN#">
SELECT #ssr_tablename#.mat_grp, #ssr_tablename#.plant, sloc, mat_id,
mat_desc, valuated_stock_unrestricted, rounding_value, rop, format, SUM(PO_Qty)
as po_qty
FROM #ssr_tablename#
INNER JOIN #tablename#
ON (#ssr_tablename#.plant = #tablename#.plant)
WHERE (#ssr_tablename#.mat_id = #tablename#.matnum)
AND #ssr_tablename#.plant IN ('SI**', 'SI**')
GROUP BY mat_id
order by #ssr_tablename#.plant, mat_id
</CFQUERY>
QueryB
---------------------------
<CFQUERY NAME="qUsage7" DATASOURCE="#DSN#">
SELECT count(distinct mdoc) AS Usage7, matnum, postdate FROM table1
INNER JOIN #ssr_tablename#
ON (table1.matnum = #ssr_tablename#.mat_id)
where plant IN ('SI**', 'SI**')
and mvt = '201'
and DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= postdate
GROUP BY matnum
order by plant;
</CFQUERY>
Output For QueryA
*************************
<cfloop query="qSSR">
<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('efefef'))#">
<cfloop index="f" list="#ssr_fields#">
<cfset bg="ffffff">
<td align="right"><cfif evaluate("#f#") eq "" OR
left(trim(evaluate("#f#")),"1") eq "0"> </cfif>#trim(evaluate("#f#"))#</td>
</cfloop>
</tr>
</cfloop>
Basically, during the loop of queryA I want it to, if it finds a match, to
output from QueryB or else just leave blank and move on to the next row...
Should this be done before hand in a query so all I have to do is output the
query data or can this be done on the backend via the CF page?
Thanks as always for your time and response(s)...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325180
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
