Outputting Query records from within loop

[jmcg]

I have a query that returns all data from a table
I then have a sub query that defines which columns should be output on the screen.
I want to be able to output data columns from the first query based on the relevant cols in the output data.

<cfquery datasource="#DSN#" name="allData">
SELECT *
FROM AllData
</cfquery>

<cfquery datasource="#DSN#" name="outputCols">
SELECT ColRef
FROM ColsData
WHERE Output = 1
</cfquery>

I then try to output these in a table

<cfoutput query="allData">
    <td>#field1#</td>
    <td>#field2#</td>
        <cfloop query="outputCols">
            <cfset x = "allData." & #ColRef#>
            <td>#evaluate(x)#</td>
        </cfloop>
</cfoutput>

This returns all output cols with the same data in each record.

I am sure this is possible but cannot see how to do it.
I have tried converting to list and arrays the output cols data but still cannot get it to work.

Any suggestions?

The tables would look like this:

All Data
Field1    Field2    Field3    Field4    Field5    Field6
1         a         d         g         j         m
2         b         e         h         k         n
3         c         f         i         l         o

OutputCol
ColRef   Output
Field3   1
Field4   1
Field3   2
Field5   2
Field6   2

 

[cfSearching]

Yes, nested loops often require a little more care. But you can achieve this by referencing the query using array notation: #queryName[colName][rowNumber]#

My preference is arrays, so I would usually convert the "outputCols" to an array. Then change the inner loop to use the array instead of a query.

Not tested, but something along these lines:

<cfset colArray = listToArray(ValueList(outputCols.ColRef))>
<cfoutput query="allData">
    <td>#field1#</td>
    <td>#field2#</td>
    <!--- Requires CF8.  For MX 7 use a from/to loop with arrayLen(...) --->
    <cfloop array="#colArray#" index="col">
        <td>#allData[col][currentRow]#</td>
    </cfloop>
</cfoutput>

----------------------------------

http://cfsearching.blogspot.com/

 

[jmcg]

Am on CF7 so have used the following.

<cfset colArray = "listToArray(ValueList(outputCols.colRef))">

<cfloop from="1" to="#ArrayLen(colArray)#" index="x">
<td>allData[col][currentRow]#</td>
</cfloop>

And get an error of:
Object of type class java.lang.String cannot be used as an array
The error occurred in \\iuser\intranet\test.cfm: line 90

88 :
89 :
90 : <cfloop from="1" to="#ArrayLen(colArray)#" index="x">
91 : <td><font face=Tahoma size=2>#allData[col][currentRow]#</td>
92 : </cfloop>

 

[LyndonOHRC]

To start with change:
<cfset colArray = "listToArray(ValueList(outputCols.colRef))">

To:
<cfset colArray = listToArray(ValueList(outputCols.colRef))>


Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[jmcg]

I had that at first but got a stranger error message and thought it was just a typo from cfSearching ;-)
Also a couple of typos on my post index="x" is actually index="col".

The error message without the inverted commas was:

[Table (rows 93 columns CREATEDDATE, SAC, SACNAME, CREATORNAME, CREATORID, SCRIPTACTION, UPDATEDBYNAME, UPDATEDBYID, UPDATEDDATE, Q01, Q02, Q03, Q04, Q05, Q06, Q07, Q08, Q09, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Q21, Q22, Q23, Q24, Q25, Q26, Q27, Q28, Q29, Q30, Q31, Q32, Q33, Q34, Q35, Q36, Q37, Q38, Q39, Q40, Q41, Q42, Q43, Q44, Q45, Q46, Q47, Q48, Q49, Q50, Q51, Q52, Q53, Q54, Q55, Q56, Q57, Q58, Q59, NOTES01, NOTES02, NOTES03, NOTES04, NOTES05, NOTES06, NOTES07, NOTES08, Q60, Q61, Q62, Q63, Q64, Q65, Q66, Q67, Q68, Q69, Q70, Q71, Q72, Q73, Q74, Q75, Q76, Q77, TREATMENT, SM, DGM, AM, SACNAME): [CREATEDDATE: coldfusion.sql.QueryColumn@166523e] [SAC: coldfusion.sql.QueryColumn@a19353] [SACNAME: coldfusion.sql.QueryColumn@1cf3bcd] [CREATORNAME: coldfusion.sql.QueryColumn@5f43f3] [CREATORID: coldfusion.sql.QueryColumn@660ad0] [SCRIPTACTION: coldfusion.sql.QueryColumn@14ff161] [UPDATEDBYNAME: coldfusion.sql.QueryColumn@1705372] [UPDATEDBYID: coldfusion.sql.QueryColumn@1768c29] [UPDATEDDATE: coldfusion.sql.QueryColumn@1ec12f7] [Q01: coldfusion.sql.QueryColumn@1d14d7b] [Q02: coldfusion.sql.QueryColumn@35f347] [Q03: coldfusion.sql.QueryColumn@1f6f2d8] [Q04: coldfusion.sql.QueryColumn@1c14f32] [Q05: coldfusion.sql.QueryColumn@36879a] [Q06: coldfusion.sql.QueryColumn@92a5] [Q07: coldfusion.sql.QueryColumn@19bc2f4] [Q08: coldfusion.sql.QueryColumn@11c3997] [Q09: coldfusion.sql.QueryColumn@8f668] [Q10: coldfusion.sql.QueryColumn@695406] [Q11: coldfusion.sql.QueryColumn@10b5726] [Q12: coldfusion.sql.QueryColumn@186a95b] [Q13: coldfusion.sql.QueryColumn@1d8b5f2] [Q14: coldfusion.sql.QueryColumn@91343a] [Q15: coldfusion.sql.QueryColumn@fbd976] [Q16: coldfusion.sql.QueryColumn@81ec20] [Q17: coldfusion.sql.QueryColumn@1b543ce] [Q18: coldfusion.sql.QueryColumn@176a908] [Q19: coldfusion.sql.QueryColumn@15370df] [Q20: coldfusion.sql.QueryColumn@11760f3] [Q21: coldfusion.sql.QueryColumn@1de32b4] [Q22: coldfusion.sql.QueryColumn@d2f47d] [Q23: coldfusion.sql.QueryColumn@1b159bd] [Q24: coldfusion.sql.QueryColumn@7aa6ef] [Q25: coldfusion.sql.QueryColumn@65b90f] [Q26: coldfusion.sql.QueryColumn@589760] [Q27: coldfusion.sql.QueryColumn@3a3451] [Q28: coldfusion.sql.QueryColumn@140dafd] [Q29: coldfusion.sql.QueryColumn@e9270c] [Q30: coldfusion.sql.QueryColumn@1028b73] [Q31: coldfusion.sql.QueryColumn@113aec] [Q32: coldfusion.sql.QueryColumn@1338d3a] [Q33: coldfusion.sql.QueryColumn@17ce094] [Q34: coldfusion.sql.QueryColumn@17c4899] [Q35: coldfusion.sql.QueryColumn@826ae9] [Q36: coldfusion.sql.QueryColumn@391778] [Q37: coldfusion.sql.QueryColumn@33ea46] [Q38: coldfusion.sql.QueryColumn@5999c4] [Q39: coldfusion.sql.QueryColumn@7e3c12] [Q40: coldfusion.sql.QueryColumn@199d789] [Q41: coldfusion.sql.QueryColumn@1158711] [Q42: coldfusion.sql.QueryColumn@56aa2b] [Q43: coldfusion.sql.QueryColumn@1be00b7] [Q44: coldfusion.sql.QueryColumn@120f04c] [Q45: coldfusion.sql.QueryColumn@18025c0] [Q46: coldfusion.sql.QueryColumn@2e6e5a] [Q47: coldfusion.sql.QueryColumn@4f1560] [Q48: coldfusion.sql.QueryColumn@49268] [Q49: coldfusion.sql.QueryColumn@28286d] [Q50: coldfusion.sql.QueryColumn@762e09] [Q51: coldfusion.sql.QueryColumn@c87f45] [Q52: coldfusion.sql.QueryColumn@11b8d77] [Q53: coldfusion.sql.QueryColumn@1e48346] [Q54: coldfusion.sql.QueryColumn@1667a0c] [Q55: coldfusion.sql.QueryColumn@d6baf5] [Q56: coldfusion.sql.QueryColumn@18c5342] [Q57: coldfusion.sql.QueryColumn@1ae4d52] [Q58: coldfusion.sql.QueryColumn@56435a] [Q59: coldfusion.sql.QueryColumn@15ff80b] [NOTES01: coldfusion.sql.QueryColumn@195c5f7] [NOTES02: coldfusion.sql.QueryColumn@1f7e31f] [NOTES03: coldfusion.sql.QueryColumn@16021da] [NOTES04: coldfusion.sql.QueryColumn@4e7405] [NOTES05: coldfusion.sql.QueryColumn@a2f77f] [NOTES06: coldfusion.sql.QueryColumn@126b747] [NOTES07: coldfusion.sql.QueryColumn@ca97ee] [NOTES08: coldfusion.sql.QueryColumn@bcbced] [Q60: coldfusion.sql.QueryColumn@1179e3f] [Q61: coldfusion.sql.QueryColumn@19665be] [Q62: coldfusion.sql.QueryColumn@f19dc2] [Q63: coldfusion.sql.QueryColumn@ac48c2] [Q64: coldfusion.sql.QueryColumn@dcf986] [Q65: coldfusion.sql.QueryColumn@181fe18] [Q66: coldfusion.sql.QueryColumn@1510062] [Q67: coldfusion.sql.QueryColumn@f04474] [Q68: coldfusion.sql.QueryColumn@1e5aba] [Q69: coldfusion.sql.QueryColumn@d68411] [Q70: coldfusion.sql.QueryColumn@1789330] [Q71: coldfusion.sql.QueryColumn@1076d7c] [Q72: coldfusion.sql.QueryColumn@a51380] [Q73: coldfusion.sql.QueryColumn@1a366fe] [Q74: coldfusion.sql.QueryColumn@beb3aa] [Q75: coldfusion.sql.QueryColumn@56889] [Q76: coldfusion.sql.QueryColumn@11235e1] [Q77: coldfusion.sql.QueryColumn@8f4e81] [TREATMENT: coldfusion.sql.QueryColumn@6b32ef] [SM: coldfusion.sql.QueryColumn@1dd2084] [DGM: coldfusion.sql.QueryColumn@81f482] [AM: coldfusion.sql.QueryColumn@1dbba75] [SACNAME: coldfusion.sql.QueryColumn@ae7c2e] ] is not indexable by 1.0

 

[LyndonOHRC]

Are you good now?

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey