none
msg 207, Invalid column name, sql 2008

    Question

  • hi every body

    could you please tell me what is the below problem?!

    SELECT [customer-grp].name as [group] ,[city-customer].name as city, 
    		customer.pk, customer.name as customer ,
    		customer.[address] ,[tel-customer].tel 
    		FROM dbo.[customer] JOIN dbo.[city-customer]
    		ON customer.[city-FK] = [city-customer].pk
    		JOIN dbo.[customer-grp] 
    		ON customer.[customerGrp-FK]= [customer-grp].pk 
    		JOIN dbo.[tel-customer] 
    		ON customer.pk  = [tel-customer].[customer-FK]
    		GROUP BY [group], city,  customer.pk


    امیدوارم همیشه بروز باشید I hope always be up2date

    Sunday, July 14, 2013 12:13 PM

Answers

  • You need to specify column names instead of aliases in the GROUP BY clause here:

    GROUP BY 
    	  [customer-grp].name
    	, [city-customer].name
    	,  customer.pk
    It is permitted to specify column aliases in an ORDER BY clause, though.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 14, 2013 12:28 PM
  • Right. If you apply a GROUP BY clause, then it needs to include all columns of the selection list. The only things that don't have to be listed (and shouldn't be listed) are all aggregates.

    So you'll have to decide for every remaining column in the Select List. For example customer.name. You can either add it to the GROUP BY clause, or change it to MAX(customer.name) in the selection list.


    Gert-Jan

    Sunday, July 14, 2013 1:08 PM

All replies

  • You need to specify column names instead of aliases in the GROUP BY clause here:

    GROUP BY 
    	  [customer-grp].name
    	, [city-customer].name
    	,  customer.pk
    It is permitted to specify column aliases in an ORDER BY clause, though.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 14, 2013 12:28 PM
  • Hi,

    Post all your table DDL or make sure you referenced the column that is present in the mentioned table .

    Try this

    SELECT [customer-grp].name as [group] ,[city-customer].name as city, 
    		customer.pk, customer.name as customer ,
    		customer.[address] ,[tel-customer].tel 
    		FROM dbo.[customer] JOIN dbo.[city-customer]
    		ON customer.[city-FK] = [city-customer].pk
    		JOIN dbo.[customer-grp] 
    		ON customer.[customerGrp-FK]= [customer-grp].pk 
    		JOIN dbo.[tel-customer] 
    		ON customer.pk  = [tel-customer].[customer-FK]
    		GROUP BY [customer-grp].name, [city-customer].name,  customer.pk

    In the group by clause you have referenced the alias names of the columns ...

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.


    Sunday, July 14, 2013 12:29 PM
  • the error changed to:

    "Msg 8120,
    Column 'dbo.customer.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

    but name is valid column in customer.


    امیدوارم همیشه بروز باشید I hope always be up2date

    Sunday, July 14, 2013 12:46 PM
  • Right. If you apply a GROUP BY clause, then it needs to include all columns of the selection list. The only things that don't have to be listed (and shouldn't be listed) are all aggregates.

    So you'll have to decide for every remaining column in the Select List. For example customer.name. You can either add it to the GROUP BY clause, or change it to MAX(customer.name) in the selection list.


    Gert-Jan

    Sunday, July 14, 2013 1:08 PM
  • MAX(customer.name) is unreasonable because name is string.

    could you please explain more about it.


    امیدوارم همیشه بروز باشید I hope always be up2date

    Sunday, July 14, 2013 1:31 PM
  • Can you please explain in more detail what you are trying to accomplish with this query?  What is the purpose of the GROUP BY?

    Without the GROUP BY, you will get detail of all customers with at least one related row in customer-grp and tel-customer, along with the selected columns.  Introducing a GROUP BY implies an aggregation to reduce the number of rows returned and there must be some rules around that.  For example, which telephone number should be returned if a customer has multiple ones?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 14, 2013 2:27 PM
  • The short answer is that you do not know how the SELECT statement works. The SELECT clause is done last, so aliases do not exist until the rest of the clause have executed. The SELECT list for a GROUP BY has to only grouped columns; that means the columns in the GROUP BY list and aggregate functions that show us group characteristics. 

    But what you did post is 1960's COBOL!! That language uses the dash in data element names, while the ANSI/ISO Standards for every language I know use the underscore. This is part of the ISO_11179 rules.

    But the worst part is your complete violation basic data modeling and the ISO_11179 rules. The basic principle is that you name a data element for what it is by its nature, never by how it is used in one table one time. A data element name has a pattern you do not know:
     “[<role>_]<attribute>_<attribute property>”. An attribute property like “_name” must be the name of something in particular in SQL. You 

    You have also split up your data into a denormalized mess. Can you fire the moron who did this to you? Can I assume a customer has one address and phone on file with the company? Does your industry use a SAN (Standard Address Number)? Your schema probably should look like this: 

    CREATE TABLE Customers
    (customer_id CHAR(10) NOT NULL PRIMARY KEY,
     customer_grp CHAR(5) NOT NULL
       CHECK (customer_grp IN (..)),
     customer_name VARCHAR(35) NOT NULL,-- usps standards
     san CHAR(10) NOT NULL 
      REFERENCES Addresses (san)
     ON UPDATE CASCADE,
     telephone_nbr CHAR(19) NOT NULL, --- E.123 standard
     ..);

    CREATE TABLE Addresses -– look up CASS Standards 
    (san CHAR(10) NOT NULL PRIMARY KEY, 
     street_address VARCHAR(35) NOT NULL,
     city_name VARCHAR(20) NOT NULL, 
     state_code CHAR(2) NOT NULL
       CHECK (state_code IN ('AK', 'AL', .. 'WY')),
     zip_code CHAR(5) NOT NULL
       CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'),

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, July 14, 2013 3:17 PM
  • Thanks for your attention and useful comment, my friends.

    there isn't any particular pattern for address and phone number. I have separate table for phone, because a customer can have several number such as tel , fax, mob

    about dash or underline, could you please tell me will it make any problem in future or not? if it's, I change all to underline.

    about my query: as you see, some tables make specification of customer. I used group by because some customers are IN one customer-grp (group), and also there is some customer in one city, and one customer may have some phone numbers

    please let me know if there is other best way to write this query.

    Thank u


    امیدوارم همیشه بروز باشید I hope always be up2date

    Sunday, July 14, 2013 7:55 PM
  • about my query: as you see, some tables make specification of customer. I used group by because some customers are IN one customer-grp (group), and also there is some customer in one city, and one customer may have some phone numbers

    please let me know if there is other best way to write this query.

    GROUP BY is not needed in order to return the group name and city.  If a customer has multiple phone numbers, do you want all of them? If so, remove the GROUP BY and let us know how the result differs from what you want.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, July 15, 2013 12:20 AM