Rename Column in CFQuery

[szeiss]

I'm trying to select data from a table using cfquery. I want to rename a few of the columns using variables, such as:

SELECT
fy as FY,
fy1_amount as #variables.FYPlus1#
FROM
tabledata

I then want to export the result to Excel.

I've looked at:

http://www.bennadel.com/blog/357-ask-ben-changing-coldfusion-query-column-names.htm

http://www.justskins.com/forums/rename-query-column-name-4060.html

I've tried the code in both of these sites and still can't get anything to work.

Thanks,
Sherry

 

[cfSearching]

Using an alias is the standard way to rename columns. What do you mean when you say you "can't get anything to work"?

I then want to export the result to Excel.

How are you doing the export? Depending on which method you are using you may be able to use whatever column headers you want and skip the rename.

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

http://cfsearching.blogspot.com/

 

[szeiss]

Here is the code that I'm using:

<cfscript>
variables.FYCURRENT = application.objFiscalCalendar.getFiscalYear();
variables.FYPLUS1 = variables.FYCURRENT + 1;
variables.FYPLUS2 = variables.FYCURRENT + 2;
variables.FYPLUS3 = variables.FYCURRENT + 3;
variables.FYPLUS4 = variables.FYCURRENT + 4;
variables.FYPLUS5 = variables.FYCURRENT + 5;
variables.MINUS1 = variables.FYCURRENT - 1;
</cfscript>

<cffunction
name="QueryChangeColumnName"
access="public"
output="false"
returntype="query"
hint="Changes the column name of the given query.">

<!--- Define arguments. --->
<cfargument
name="Query"
type="query"
required="true"
/>

<cfargument
name="ColumnName"
type="string"
required="true"
/>

<cfargument
name="NewColumnName"
type="string"
required="true"
/>

<cfscript>

// Define the local scope.
var LOCAL = StructNew();

// Get the list of column names. We have to get this
// from the query itself as the "ColdFusion" query
// may have had an updated column list.
LOCAL.Columns = ARGUMENTS.Query.GetColumnNames();

// Convert to a list so we can find the column name.
// This version of the array does not have indexOf
// type functionality we can use.
LOCAL.ColumnList = ArrayToList(
LOCAL.Columns
);

// Get the index of the column name.
LOCAL.ColumnIndex = ListFindNoCase(
LOCAL.ColumnList,
ARGUMENTS.ColumnName
);

// Make sure we have found a column.
if (LOCAL.ColumnIndex){

// Update the column name. We have to create
// our own array based on the list since we
// cannot directly update the array passed
// back from the query object.
LOCAL.Columns = ListToArray(
LOCAL.ColumnList
);

LOCAL.Columns[ LOCAL.ColumnIndex ] = ARGUMENTS.NewColumnName;

// Set the column names.
ARGUMENTS.Query.SetColumnNames(
LOCAL.Columns
);
variables.COLUMN_LIST = LOCAL.ColumnList;
}

// Return the query reference.
return( ARGUMENTS.Query );

</cfscript>
</cffunction>

<!--- Call the function that gets column names --->
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"dept_name","Dept"),"project_id","ProjectID")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"project_name","ProjectName"),"planning_project","PlanProject")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"responsible_dept_cd","RespDept"),"print_plan_page","PrintPP")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy","FY"),"actuals","Actuals")/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy1_amount",'FY#variables.FYCURRENT#'),"fy2_amount",'#variables.FYPLUS1#')/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy3_amount",'#variables.FYPLUS2#'),"fy4_amount",'#variables.FYPLUS3#')/>
<cfset QueryChangeColumnName(QueryChangeColumnName(variables.theReport,"fy5_amount",'#variables.FYPLUS4#'),"fy6_amount",'#variables.FYPLUS5#')/>




<!--- list of variables for columns --->
<cfset variables.reportitems = variables.COLUMN_LIST>

<!--- need to order by the reportitems, reason for using a query --->
<cfquery name="qgetdata" dbtype="query">
select * from theReportData order by #reportitems#
</cfquery>

<cfset variables.columns = variables.reportitems>

<!--- set vars for special chars --->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>

<!--- output data using cfloop & cfoutput --->
<cfsavecontent variable="variables.body">
<cfoutput>
<table border="1">
<tr>
<cfloop list="#variables.columns#" index="col">
<td bgcolor="##C0C0C0"><b>#col##TabChar#</b></td>
</cfloop>
</tr>
<cfloop query="qGetData">
<tr>
<cfloop index="col" list="#variables.columns#">
<td>#qGetData[col][currentRow]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
</cfsavecontent>

<cffile action="write" file="#variables.fileLocation#" output="#variables.body#" >
<cfheader name="content-disposition" value="#variables.CFHeaderValue#">
<cfcontent type="text/xls" file="#variables.fileLocation#">

I get the following error:
Query Of Queries runtime error.
The select column reference [DEPT] is not a column in any of the tables of the FROM table list.

I have the following in a reports.cfc:

<cffunction name="GetAppr" access="public" output="false" returntype="query">
<cfargument name="username" required="true" type="string">

<cfquery datasource="#application.dsn#" name="GetAppr">
SELECT
dept_name,
project_id,
project_name,
planning_project,
responsible_dept_cd,
print_plan_page,
fy,
actuals,
fy1_amount,
fy2_amount,
fy3_amount,
fy4_amount,
fy5_amount,
fy6_amount
FROM
cip.cip_report_approp_plan_bu
WHERE
username = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.username#">
ORDER BY
project_id
</cfquery>

<cfreturn GetAppr />

</cffunction>

 

[cfSearching]

As an aside, technically you do not have to rename the columns at all. You could simply use two different lists: one containing the query columns and the other containing the headers you wish to display. As long as the values are in the same order, it would work just as well.

QueryChangeColumnName(variables.theReport
...
<!--- need to order by the reportitems, reason for using a query --->
<cfquery name="qgetdata" dbtype="query">
    select * from theReportData order by #reportitems#
</cfquery>

I am still looking at the code, but one thing jumps out at me. If you are renaming the columns in query object #variables.theReport#, it seems like that is the query object that should be used in the QoQ, not theReportData.

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

http://cfsearching.blogspot.com/

 

[szeiss]

Do you have an example of creating 2 lists as you mentioned above.

Thanks

 

[cfSearching]

In the simplest form, just create them manually ensuring the values are in the proper order. Note, some of the columns were omitted for brevity.

<cfset variables.displayColumns = "Dept,ProjectID,ProjectName,PlanProject">
<cfset variables.dataColumns = "dept_name,project_id,project_name,planning_project">

Then use #variables.displayColumns# in the header loop and #variables.dataColumns# in the data loop.

<!--- output data using cfloop & cfoutput --->
<cfsavecontent variable="variables.body">
<cfoutput>
        <table border="1">
            <tr>
                <cfloop list="#variables.displayColumns#" index="col">
                    <td  bgcolor="##C0C0C0"><b>#col##TabChar#</b></td>
                </cfloop>
            </tr>
            <cfloop query="qGetData">
                <tr>
                    <cfloop index="col" list="#variables.dataColumns#">
                        <td>#qGetData[col][currentRow]#</td>
                    </cfloop>
                </tr>
            </cfloop>
        </table>
    </cfoutput>
</cfsavecontent>

But I think your error is related to using the wrong query object in the QoQ. Try using #theReport# instead of #theReportData#

<cfquery name="qgetdata" dbtype="query">
    select * from variables.theReport order by #reportitems#
</cfquery>

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

http://cfsearching.blogspot.com/

 

[szeiss]

I get the following error changing from 'theReportData' to 'variables.theReport'

Error Executing Database Query.

Query Of Queries runtime error.
The ORDER BY position number is out of range of the number of items in the select list

which corresponds to this line:

101 : <!--- need to order by the reportitems, reason for using a query --->
102 : <cfquery name="qgetdata" dbtype="query">
103 : select * from variables.theReport order by #reportitems#
104 : </cfquery>

Thanks

 

[cfSearching]

It is hard to troubleshoot without knowing the contents of the either variable. Especially when SELECT * is used. But have you cfdumped the query object and the #reportitems# variables? What are the columns in the query versus your ORDER BY clause?

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

http://cfsearching.blogspot.com/

 

[szeiss]

Thank you for your help, I really appreciate it. I took what you suggested and it worked exactly as I wanted it to. This will help me with other reports also.

<cfcase value="APPROPRIATIONPLANBU_XL">
<cfscript>
variables.FYCURRENT = application.objFiscalCalendar.getFiscalYear();
variables.FYPLUS1 = variables.FYCURRENT + 1;
variables.FYPLUS2 = variables.FYCURRENT + 2;
variables.FYPLUS3 = variables.FYCURRENT + 3;
variables.FYPLUS4 = variables.FYCURRENT + 4;
variables.FYPLUS5 = variables.FYCURRENT + 5;
variables.MINUS1 = variables.FYCURRENT - 1;
attributes.USERNAME = Session.userinfo.EMPLOYEE_ID & "." & randRange(0, 9) & randRange(0, 9) & randRange(0, 9) & randRange(0, 9);
</cfscript>
<cfinclude template="appropriation_plan_bu_act.cfm">
<cfscript>
variables.reports = createObject('component','cfcs.reports');
variables.theReport = variables.reports.GetAppr(attributes.username);
variables.theReportData = variables.reports.GetAppr(attributes.username);
variables.CFHeaderValue = 'attachment;filename='&'#attributes.fuseaction#'&'.xls';
variables.fileLocation = 'C:\'&'#attributes.fuseaction#'&'.xls';
variables.body = '';
</cfscript>
<cfif variables.formErrors>
<cfinclude template="qry_get_report_link_info.cfm">
<cfinclude template="dsp_no_data_found.cfm">
<cfinclude template="appropriation_plan_bu_frm.cfm">
</cfif>

<cfset variables.displayColumns = "Dept,ProjectID,ProjectName,PlanProject,RespDept,PrintPP,FY,Actuals,#variables.FYCURRENT#,#variables.FYPLUS1#,#variables.FYPLUS2#,#variables.FYPLUS3#,#variables.FYPLUS4#,#variables.FYPLUS5#">
<cfset variables.dataColumns = "dept_name,project_id,project_name,planning_project,responsible_dept_cd,print_plan_page,fy,actuals,fy1_amount,fy2_amount,fy3_amount,fy4_amount,fy5_amount,fy6_amount">

<!--- set vars for special chars --->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>

<!--- output data using cfloop & cfoutput --->
<cfsavecontent variable="variables.body">
<cfoutput>
<table border="1">
<tr>
<cfloop list="#variables.displayColumns#" index="col">
<td bgcolor="##C0C0C0"><b>#col##TabChar#</b></td>
</cfloop>
</tr>
<cfloop query="variables.theReportData">
<tr>
<cfloop index="col" list="#variables.dataColumns#">
<td>#variables.theReportData[col][currentRow]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
</cfsavecontent>

<cffile action="write" file="#variables.fileLocation#" output="#variables.body#" >
<cfheader name="content-disposition" value="#variables.CFHeaderValue#">
<cfcontent type="text/xls" file="#variables.fileLocation#">
</cfcase>

 

[cfSearching]

I get the following error changing from 'theReportData' to 'variables.theReport'

Error Executing Database Query.

Query Of Queries runtime error.
The ORDER BY position number is out of range of the number of items in the select list

For what it is worth, I finally had time to figure out what that error means.

Within a QoQ, you can ORDER BY a column name or a column position. Where position 1 = first column in the query, 2 = second column, etcetera..

Some of your column names are completely numeric, such as 2008, 2009, etcetera. The QoQ thinks those are positions, not column names. This causes an error because the query does not contain 2009 columns ;-)

Changing the column name to something like "FY2009" should solve the problem.







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

http://cfsearching.blogspot.com/

 

[szeiss]

Thank you that's good to know. Thanks again for your help.