Getting complex object error when trying to output query values

Question

My goal is to just output the column data specified in the "fieldList".

Getting the following error:

Complex object types cannot be converted to simple values. The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values. The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a cfif tag. The error occurred on line 20.

When trying to do the following:

<cfquery datasource="retailers" name="myQuery">
Select * FROM retailer 
WHERE retailer_id = '#url.id#'
</cfquery>

<cfset fieldList = "company,phone,phone_secondary,fax,email,website">
<cfloop list="#fieldList#" index="i">      
#myQuery[i]#
</cfloop>

Shouldn't this work without giving me an error? I feel like I'm just overlooking something simple I just can't find the answer anywhere.

Clarify: are you trying to output a list of column names from the query result? Or do you simply wish to do the most obvious--which is: loop over the results of the query (ie. all the rows of data) and output them. I ask because you aren't specific about what you want, and the code (as written) could potentially go either way. — Shawn Holmes

Shawn Holmes Shawn Holmes · Accepted Answer · 2012-03-08T23:27:25

Ok, I see you ammended your initial code, so here's my response:

You are looping over a list of columns, and trying to evaluate each column as though it is a single element in a struct.

A query, however, is slightly different: it is accessed as a series of structs, which in turn, are arrays--of each row of data as they return from the query.

If you want to output all of the rows of data, without knowing the columns up front (or passing them in dynamically as you are implying in your code above), try this:

<cfoutput query="myQuery">
  <cfloop list="#myQuery.ColumnList#" index="thisColumn">
    #myQuery[thisColumn][myQuery.CurrentRow]#
  </cfloop>
</cfoutput>

This will provide you with the output you need. The outer cfoutput with the query attribute will loop over all the rows of data you received. Then, for each row, you loop over the list of columns the query produces, and for each column, output the data, specifying the row via myQuery.CurrentRow, which iterates automatically for you via the outer output.

I'd also take a moment now to advocate that you try to stay away from loops within loops, and just output your values explicitly:

<cfoutput query="myQuery">
  #company# #phone#
</cfoutput>

Using this syntax is slightly less expensive than the aforementioned loop within a loop.

Getting complex object error when trying to output query values

1 Answers