This content has been marked as final. Show 5 replies
Use cfhttp to read the two files and use the name attribute to make them query objects. Then use Query of Queries to get your answers.
I have arrived at a partial solution based on your (very appreciated) advice that I use a query of query. The following is the last of 4 recordsets that I used to get my data. However, because in the WHERE clause I ask for matching B/L (order) numbers, I do not get a full data listing. (Yet I need the mutual primary key for each file). What I need are all of the records from Recordset2 and only those from Recordset3 that have a matching B/L#. I do not want to lose records just because the order has not yet been billed. What else do I need to implement? Thanks!
<cfquery name="Recordset4" dbtype="query">
SELECT Recordset3.RBLNO as BL, ((Recordset3.RCUTR - 100) / 100) as CUT,(Recordset2.DTAMTsum * -1) as LH, (Recordset2.DTAMTsum * ((Recordset3.RCUTR - 100) / 100)) as NET
FROM Recordset3, Recordset2
WHERE Recordset3.RBLNO = Recordset2.DTBLNO
Do more than one Q of Q. Be imaginative with valuelists and union queries.
I did get the code to work using a Union. However, the results are not what I sought. What I seek is to use a query of queries on two queries that will pull data from each into a single recordset that is limited in its record count to the corresponding record count of the registration table. The field taken from the second query I would like as part of the resulting recordset. I do not desire a second record for the same item containing that field that is created in my successful UNION.
I recreated the scenario in MS Access and accomplished exactly what I want with the following SQL:
SELECT REGS.*, [DATA-Grouped-Qry].SumOfLH
FROM REGS LEFT JOIN [DATA-Grouped-Qry] ON REGS.RBLNO = [DATA-Grouped-Qry].BL;
Can you recommend the best method to address this in ColdFusion. Thanks so much.
Something like this
select fields, 0 as thesum
and somefield not in (a valuelist )
select fields, sum(something) as thesum
and somefield in (the same valuelist )
group by fields