Changing the field names in a CFQUERY object

[MartinW-2006]

Hello, I wonder whether you know the answer to this?

I want to change the names of the fields in a CFQUERY result set after I have retrieved the results from the database (ie. I can't use "AS" in the original SQL statement). I can think of 2 ways to do this - use the query-a-query (CFSQL) feature or just build and populate a new query object, as set out below:

<cfquery name="CustList" datasource="name">
SELECT CustId,CustName,CustAddress FROM Customers
WHERE OrderStatus='P'
</cfquery>

OPTION#1:

<cfqueryname="GenericList" type="query">
SELECT CustId AS Id,CustName AS Name,CustAddress AS Address FROM CustList
</cfquery>

OPTION#2:

<cfset GenericList = QueryNew("Id,Name,Address")>
<cfloop query="CustList">
<cfset unused = QueryAddRow(GenericList)>
<cfset unused = QuerySetCell(GenericList,"Id",CustList.CustId)>
<cfset unused = QuerySetCell(GenericList,"Name",CustList.CustName)>
<cfset unused = QuerySetCell(GenericList,"Address",CustList.CustAddress)>
</cfloop>

Do you know which of these would be more efficient? And is there a better way, whereby I can maniplulate the names of the columns in the original query (ie. so that I can rename the "CustName" field to "Name" and then use "CustList.Name" directly)?

Thanks!

[Stefan_K_]

use the query-a-query (CFSQL) feature to give new aliases.
In my exp. the performance cost are close to zero.

What's the point in changing the names anyway?
Might want to consider that question first.

[MartinW-2006]

Thanks guys!

If there is a close to zero overhead to the simple query-a-query then that seems like the way to go, with no need to look for a means of manipulating or modifying the original query.

The reason I want to change the field names is so that the rest of my code can contain references to "CustList.Name" whereas otherwise I would have to do something like "Evaluate('CustList.#NameField#')" which is horribly messy. And the way the original query is generated means I can't use "AS" in it, but I still want to use the same code to work with that query result and other query results that share similar/related data with different field names.

[Stefan_K_]

.

[Stefan_K_]

quote:

---

Evaluate('CustList.#NameField#')

---

There are more efficient ways to do this, Evaluate() should be hardly ever used.

Please note the use of the variable CurrentRow in the following example. It's the internal counter in cfloop/cfoutput-over-queries.
Same as in <cfloop index="CurrentRow" from="1" to="#myQuery.RecordCount#">

Example:

[MartinW-2006 · Accepted solution]

Thanks Stefan! Ignore the "guys" bit below, I didn't originally notice the first two responses both came from you!

[MartinW-2006]

Thanks!

[Stefan_K_]

Your option 2 would be greatly sped up by the following, but option 1 still wins IMHO:

<cfset qDump = QueryNew("dummyColumn")>
<cfloop index="iColumn" list="#qGetUserData.ColumnList#">
<cfset QueryAddColumn( qDump, iColumn, qGetUserData[iColumn] )>
</cfloop>

Of course you have an extra, empty column "dummyColumn" in the end ....