Strange Recursive Query of Query error.
It seems to involve a field in my main query that can contain null or integer values.
AreaList recordset
SELECT a.ID AS a_id, a.NAME AS a_name, a.PARENTID, a.geo_order AS a_order,
l.id AS l_id, l.name AS l_name, l.geo_order AS l_order,
j.areaId
FROM Area a LEFT OUTER JOIN
Location_Area_Join j ON (a.id = j.areaId) LEFT OUTER JOIN
Locations l ON (j.locationid = l.id)
ORDER BY a.ParentID, a.ID, l.name
The a.geo_order and l.geo_order fields can contain either nulls or integers.
I then run this recursive function to output that query in a parent-child tree relationship.
area_recurse function
<cffunction name="area_recurse" returntype="string" output="false">
<cfargument name="parentid" required="true" type="numeric">
<cfset var returnVar = "">
<cfset var oneLevel = "">
<cfquery dbtype="query" name="oneLevel">
SELECT *
FROM AreaList
WHERE parentid = #arguments.parentid#
ORDER BY a_order, a_name, l_order, l_name
</cfquery>
<cfsavecontent variable="returnVar">
<cfdump var="#oneLevel#">
<ul>
<cfoutput query="oneLevel" group="areaid">
<li><input type="text" size="1" maxlength="3" name="a-#a_id#" value="#a_order#"/> <strong>Area: #a_id# (#parentid#) #a_name#</strong> #killme#
<cfif len(trim(areaid)) GT 0>
<ul class="locations">
<cfoutput>
<li><input type="text" size="1" maxlength="3" name="l-#l_id#" value="#l_order#"/> Location: #l_id# (#areaid#) #l_name#</li>
</cfoutput>
</ul>
</cfif>
#area_recurse(a_id)#
</li>
</cfoutput>
</ul>
</cfsavecontent>
<cfreturn returnVar>
</cffunction>
If I run this code as is, it will only display the first record of each query-of-query record set. But a dump of the record set in each iteration of the function shows that the query of query generated the complete and correct record set. But the <cfoutput query...> loop does not loop over them. This can be seen here: http://www.sierraoutdoorrecreation.com/Geo-Order.cfm. If I modify the base query to provide a default integer for the geo-order fields or remove the geo-order fields from the query-of-query SQL statement, it all works as expected. The loops will loop over all the complete record sets for every iteration and display the entire tree. Does this make sense to anybody? Is this expected behavior somehow? How does the <cfdump...> logic display the entire record sets, but my <cfoutput query...> loops do not?
TIA
Ian
P.S. I am not really looking for replacements for my tree building logic, I know how to do that. I am just wanting to understand this particular strange behavior.
AdChoices