Hello, all,
Firstly, let me state that I am helping another developer with a project that he is working on. I am wrenching someone else’s code, mostly, and adding some of my own to it. I am not the one who decided to put a looped output within a looped output. I much prefer to aggregate my data another way, whenever possible.
That being said: I am working on a ColdFusion web app. It is pulling data from an Oracle database. I have one query generating output, and in some circumstances based upon a conditional, another query is outputting data within that first output. If said conditional is met, then the second query is getting column names to generate a customized set of tabular data (some queries will have five columns, others up to sixteen.)
I am trying to create a query object and populate it from the combined queries; this will be passed to another page (in JSON format) to be used to populate an Excel object.
The issue that I am facing is that the query object that I am creating will have ten times the number of records that the original query has, even though I’m building the query object as the display is being built. Once the actual records are displayed (ie, the display query is done at, say, 38 records), the query object will reflect the same records, then add nine times that many records that are all blank to the query object.
Here is some pseudocode of what it was doing before I got to it:
select *
from report_A, report_B
where report_A.columnid = report_B.columnid
order by report_col_order
The above query is called ‘reportcolumns’.
<tr>
<cfoutput query="reportcolumns">
<th>#report_column#</th>
</cfoutput>
</tr>
<cfoutput query="person">
<tr>
<cfoutput query="reportcolumns">
<td>#person[reportcolumns.report_column][person.currentRow]#</td>
</cfoutput>
</tr>
</cfoutput>
This does exactly what it is supposed to do - output a dynamic list of column names then populate with the corresponding data.
However, if I add my query object creating code, the display will still do what it’s supposed to do, but the query object that is built has ten times the number of records that the display outputs - the original query data plus nine times that many rows of blank records.
<tr>
<cfoutput query="reportcolumns">
<th>#report_column#</th>
<cfset variables.columnList = ListAppend(variables.columnList,trim(REreplaceNoCase(report_columns,'[^[:word:]]','_','all'))) />
</cfoutput>
<cfset variables.clLen = listLen(variables.columnList,',') /><cfloop from="1" to="#val(variables.clLen)#" step="1" index="idx"><cfset variables.columnType = ListAppend(variables.columnType,"VARCHAR") /></cfloop>
<cfset variables.rosterQry = QueryNew(variables["columnList"],variables["columnType"]) />
<cfset tmp = queryAddRow(variables.rosterQry,1) />
<cfset tmp = querySetCell(variables.rosterQry,'Name',lname & ', ' & fname) />
<cfset tmp = querySetCell(variables.rosterQry,'Directorate',dir) />
<cfset tmp = querySetCell(variables.rosterQry,'Division',div) />
</tr>
<cfoutput query="person">
<tr>
<cfoutput query="reportcolumns">
<td>#person[reportcolumns.report_column][person.currentRow]#</td>
<cfset tmp = querySetCell(
variables.rosterQry,
ListGetAt(variables.columnList,reportcolumns.currentrow,','),
person[reportcolumns.db_column_name][person.currentrow],
person.currentRow
) />
</cfoutput>
</tr>
</cfoutput>
The display shows 38 records, the query object contains over 380 records - 1 through 38 are correctly populated with data, 39 through 380+ are all blank.
I’m tired… I’m pretty sure I’m missing something simple. What is causing the object to have ten times as many records?
V/r,
