Name of object not valid SQL

Question

Name of object not valid SQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I want to create a function that allows generating a sales report per employee, in a certain year. The function must return: the data of the employee, the number of registered orders and the total amount per employee. This is an exercise of a PDF and I want to adapt it to the Northwind database to practice.

This is my function

CREATE FUNCTION REPORTEVENTAS(@ANO INT) RETURNS TABLE
AS

RETURN (SELECT EmployeeID AS 'ID',
     FirstName AS 'NOMBRE',
     LastName AS 'APELLIDO',
     COUNT(OD.OrderID) AS 'CANTIDAD',
     SUM(Quantity*UnitPrice)
     FROM [Order Details] OD
     INNER JOIN Orders O ON O.OrderID = OD.OrderID
     INNER JOIN Employees E ON E.EmployeeID = O.EmployeeID
     WHERE YEAR(O.OrderDate) = @ANO
     GROUP BY 'ID','NOMBRE')

This is the error that is giving me

Msg 164, Level 15, State 1, Procedure REPORTEVENTAS, Line 13
Each GROUP BY expression must contain at least one column that is not an 
outer reference.

This is the version of SQL I'm using

Microsoft SQL Server 2012 - 11.0.2218.0 (X64) 
Jun 12 2012 13:05:25 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

sql sql-server

asked by Marco 30.07.2018 в 21:06

source

2 answers

Execute .exe from html How can I set a date in the current day?

score 0 · Answer 1

If in the joins of the tables you put an alias, you must reference the fields with the same alias, otherwise you will generate an error, also in the gurop by is ordered by the field ... try this way:

SELECT E.EmployeeID AS 'ID',
     E.FirstName AS 'NOMBRE',
     E.LastName AS 'APELLIDO',
     COUNT(OD.OrderID) AS 'CANTIDAD',
     SUM(Quantity*UnitPrice)
     FROM [Order Details] OD
     INNER JOIN Orders O ON O.OrderID = OD.OrderID
     INNER JOIN Employees E ON E.EmployeeID = O.EmployeeID
     WHERE YEAR(O.OrderDate) = @ANO
     GROUP BY 'E.EmployeeID','E.FirstName'

I hope it serves you ... ReNiceCode!

score 0 · Answer 2

It may be because you are not recognizing it since BD comes from the object:

Try this way:

 CREATE FUNCTION REPORTEVENTAS(@ANO INT) RETURNS TABLE
 AS

 RETURN (SELECT E.EmployeeID AS 'ID',
 E.FirstName AS 'NOMBRE',
 E.LastName AS 'APELLIDO',
 COUNT(OD.OrderID) AS 'CANTIDAD',
 SUM(Quantity*UnitPrice)
 FROM [NOMBRE_BD].[Order Details] OD
 INNER JOIN [NOMBRE_BD].[Orders] O ON O.OrderID = OD.OrderID
 INNER JOIN [NOMBRE_BD].[Employees] E ON E.EmployeeID = O.EmployeeID
 WHERE YEAR(O.OrderDate) = @ANO
 GROUP BY 'E.EmployeeID','E.FirstName')

In [NAME_BD] remember to replace with the name of the Database.