What am I doing wrong here? This is driving me nuts!

Report · Oct 20, 2009

I cannot figure out what I am doing wrong here. This for displays the first line and then stops. WHY???? I am including the code and the error message. Please help!!

<cfquery datasource="manna_premier" name="zone_report">
SELECT DISTINCTROW Users.UserID, Users.UserFirstName, Users.UserLastName, Users.UserZone, First(Orders.SaleDate) AS [First Of SaleDate], First(Orders.TerritoryManager) AS [First Of TerritoryManager], Sum(Orders.UserID) AS [Sum Of Orders_UserID], Count(*) AS [Count Of Orders]
FROM Users INNER JOIN Orders ON Users.[UserID] = Orders.[UserID]
WHERE SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
GROUP BY Users.UserID, Users.UserFirstName, Users.UserLastName, Users.UserZone;
</cfquery>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Manna Premier - ZVP Report</title>
<style type="text/css">

</style>
</head>

<body>

<cfoutput>
<span class="style1"><font face="Tahoma">ZVP Report from #FORM.Start# to #FORM.End#.<br />
</font></span></cfoutput>

<cfoutput query="zone_report" group="UserZone">
<span class="style1"><font face="Tahoma">#UserZone# Zone</font></span><br />

<cfoutput group="Users.UserID">
<span class="style3"><font face="Tahoma">#UserLastName#,#UserFirstName#</font></span><br />

    <td></td>
    <td>#zone_report.RecordCount#</td>
    <td></td>
    <td>TotalOrders</td>
    <td>ClosePercentage</td>
    <td>TotalItems</td>
    <td>TotalNewItems</td>
    <td> </td>
</tr></cfoutput>

</table>

</body>
</html>
</cfoutput>
</cfoutput>

Here is the error message;

The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.

Error Occurred While Processing Request

Invalid column name for the group attribute.
The value specified for the group attribute Users.UserID does not specify a column name of the query.

The error occurred in D:\Inetpub\mannapremier\zvp_report2.cfm: line 45
43 : 44 : <td></td> 45 : <td>#zone_report.RecordCount#</td> 46 : <td></td> 47 : <td>TotalOrders</td>

Report · Oct 20, 2009

<cfoutput group="Users.UserID">
...
the group attribute "Users.UserID" does not specify a column name of the the query.

Not to be insulting, but did you read the error message 😉 ? Though the wording could be better, it is clearly saying "Users.UserID" is not a column in your query. You are mistakenly using the SQL table alias in your reference. Notice how your first group statement works? It is because you are _not_ using the alias here:

Report · Oct 20, 2009

I don't understand because Users.UserID is the very first thing listed in my SELECT statement. I am completely self taught and some of the most obvious stuff completely slips by me.

Report · Oct 20, 2009

Well, in your query you are prefixing the column name "UserID" with the name of the source table: "Users".

SELECT DISTINCTROW TableName.UserID

The source table name is only used by your database. CF does not know or care what table a column came from. It just passes the sql code off to your database. When your database returns the information to cfquery, all CF sees is the names of the columns, not their source tables. So it sees the column names as only:

UserID ... not Users.UserID

UserZone ... not Users.UserZone

... etcetera ...

So the column name in the "group" should be "UserID" ... not "Users.UserID". Because CF has no idea what that means. If you ever want to view the names of the columns in your query, just dump the "columnList" variable:

...the SQL table alias

I meant to say table name, not alias.

Report · Nov 01, 2009

Thanks -==cfSearching==-, you're a lifesaver!

What am I doing wrong here? This is driving me nuts!

Invalid column name for the group attribute.

1 Correct answer