7 Replies Latest reply on Feb 28, 2008 8:40 AM by MartinW-2006

    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!