Skip to main content
A
Anonymous
October 21, 2009
Answered

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

  • October 21, 2009
  • 1 reply
  • 1567 views

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">
<!--
.style1 {
font-size: 18px;
font-weight: bold;
}
.style3 {font-size: 14px; font-weight: bold; }
-->
</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 />


<table width="500" border="0">
  <tr><cfoutput>

    <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>

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    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:

    <cfdump var="#yourQueryName.columnList#">

    ...the SQL table alias

    I meant to say table name, not alias.

    1 reply

    -
    -__cfSearching__-
    Inspiring
    October 21, 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:

    <cfoutput query="zone_report" group="UserZone">

    A
    Anonymous
    October 21, 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.

    -
    -__cfSearching__-Correct answer
    Inspiring
    October 21, 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:

    <cfdump var="#yourQueryName.columnList#">

    ...the SQL table alias

    I meant to say table name, not alias.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices