 |
|
|
|
Next: Stored Procedure question
|
| Author |
Message |
External
Since: Jul 12, 2006
Posts: 13
|
(Msg. 1) Posted: Thu Dec 28, 2006 1:05 pm
Post subject: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hi,
I have a stored procedure and when I execute it, I always get the
following error:
"Column 'DistributionPrintReceiptInstance.DistributionCommonInstanceID'
is invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause."
Could anyone tell me how to get this fixed in the following stored
procedure:
-----------------------------------------------------------------------------------------------------------------------------
CREATE Procedure dbo.SPusrReportsPrintDistributed
AS
IF OBJECT_ID('tempdb..#PrintableReports') IS NOT NULL
DROP TABLE #PrintableReports
CREATE TABLE #PrintableReports (
AccountNumber int,
MailingName varchar(40),
Full_Name varchar(80),
Location varchar(80),
AccountName varchar(80),
Rn_Descriptor varchar(80)
)
INSERT INTO #PrintableReports
(
AccountNumber,
MailingName,
Full_Name,
Location,
AccountName,
Rn_Descriptor
)
EXEC PIVOTALVIEW...SPcrpGetReportsPrintDistributed
SELECT TOP 65536 -- "65536" so we don't accidentally overwhelm
the caller UI (flex-grid specifically)
AccountNumber,
MailingName,
Full_Name AS 'Client Name',
Location,
AccountName,
ri.ReportInstanceID,
CASE WHEN (ri.Status LIKE '%COMPLETE%' AND
(pri.ReceiptTime <> '' AND pri.ReceiptTime <= GetDate())) -- Scanned
THEN 'Scanned'
WHEN (ri.Status LIKE '%COMPLETE%' AND
(pri.ReceiptTime = '')) -- Printed only (not scanned)
THEN 'Printed'
END AS 'Print Receipt Status',
pri.ReceiptUserName,
pri.ReceiptTime,
dci.DistributionCommonInstanceID
FROM #PrintableReports
INNER JOIN ReportInstance ri
ON #PrintableReports.AccountNumber = ri.ReportInstanceID
INNER JOIN StepInstance si
ON si.ReportInstanceID = ri.ReportInstanceID
INNER JOIN DistributionInstance di
ON si.StepInstanceID = di.StepInstanceID
INNER JOIN DistributionCommonInstance dci
ON di.DistributionInstanceID = dci.DistributionInstanceID
-- Get the latest scann user and time
LEFT JOIN
( SELECT DistributionCommonInstanceID,
MAX (ReceiptTime) MaxReceiptTime
FROM
DistributionPrintReceiptInstance
GROUP BY
DistributionPrintReceiptInstanceID
) mpri
ON mpri.DistributionCommonInstanceID =
dci.DistributionCommonInstanceID
LEFT JOIN DistributionPrintReceiptInstance pri
ON pri.DistributionCommonInstanceID =
mpri.DistributionCommonInstanceID
and pri.ReceiptTime = mpri.MaxReceiptTime
WHERE
si.StepInstanceName = 'Distribute Reports' -- Filter StepInstance by
relevant step, because there're multiple step instances for each report
instance
-- Clear the temporary table
DROP TABLE #PrintableReports
GO
IF OBJECT_ID('dbo.SPusrReportsPrintDistributed') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.SPusrReportsPrintDistributed >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE
dbo.SPusrReportsPrintDistributed >>>'
GO
GRANT EXECUTE ON dbo.SPusrReportsPrintDistributed TO CRPConsoleUser,
CRPConsoleUserFullAccess, CRPConsoleUserScanOnly
GO |
|
| Back to top |
|
 | |
External
Since: Oct 26, 2006
Posts: 13
|
(Msg. 2) Posted: Thu Dec 28, 2006 1:11 pm
Post subject: Re: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Change the group by .. see comment inline
> ( SELECT DistributionCommonInstanceID,
> MAX (ReceiptTime) MaxReceiptTime
> FROM
> DistributionPrintReceiptInstance
> GROUP BY
> DistributionPrintReceiptInstanceID -- This should probably be DistributionCommonInstanceID
> ) mpri
Best
Payson
Emily wrote:
> Hi,
>
> I have a stored procedure and when I execute it, I always get the
> following error:
>
> "Column 'DistributionPrintReceiptInstance.DistributionCommonInstanceID'
> is invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
>
> Could anyone tell me how to get this fixed in the following stored
> procedure:
>
> -----------------------------------------------------------------------------------------------------------------------------
> CREATE Procedure dbo.SPusrReportsPrintDistributed
> AS
>
> IF OBJECT_ID('tempdb..#PrintableReports') IS NOT NULL
> DROP TABLE #PrintableReports
>
> CREATE TABLE #PrintableReports (
> AccountNumber int,
> MailingName varchar(40),
> Full_Name varchar(80),
> Location varchar(80),
> AccountName varchar(80),
> Rn_Descriptor varchar(80)
> )
>
> INSERT INTO #PrintableReports
> (
> AccountNumber,
> MailingName,
> Full_Name,
> Location,
> AccountName,
> Rn_Descriptor
> )
> EXEC PIVOTALVIEW...SPcrpGetReportsPrintDistributed
>
>
> SELECT TOP 65536 -- "65536" so we don't accidentally overwhelm
> the caller UI (flex-grid specifically)
> AccountNumber,
> MailingName,
> Full_Name AS 'Client Name',
> Location,
> AccountName,
> ri.ReportInstanceID,
> CASE WHEN (ri.Status LIKE '%COMPLETE%' AND
> (pri.ReceiptTime <> '' AND pri.ReceiptTime <= GetDate())) -- Scanned
> THEN 'Scanned'
> WHEN (ri.Status LIKE '%COMPLETE%' AND
> (pri.ReceiptTime = '')) -- Printed only (not scanned)
> THEN 'Printed'
> END AS 'Print Receipt Status',
> pri.ReceiptUserName,
> pri.ReceiptTime,
> dci.DistributionCommonInstanceID
>
> FROM #PrintableReports
> INNER JOIN ReportInstance ri
> ON #PrintableReports.AccountNumber = ri.ReportInstanceID
> INNER JOIN StepInstance si
> ON si.ReportInstanceID = ri.ReportInstanceID
> INNER JOIN DistributionInstance di
> ON si.StepInstanceID = di.StepInstanceID
> INNER JOIN DistributionCommonInstance dci
> ON di.DistributionInstanceID = dci.DistributionInstanceID
>
> -- Get the latest scann user and time
> LEFT JOIN
> ( SELECT DistributionCommonInstanceID,
> MAX (ReceiptTime) MaxReceiptTime
> FROM
> DistributionPrintReceiptInstance
> GROUP BY
> DistributionPrintReceiptInstanceID
> ) mpri
> ON mpri.DistributionCommonInstanceID =
> dci.DistributionCommonInstanceID
>
> LEFT JOIN DistributionPrintReceiptInstance pri
> ON pri.DistributionCommonInstanceID =
> mpri.DistributionCommonInstanceID
> and pri.ReceiptTime = mpri.MaxReceiptTime
>
>
> WHERE
> si.StepInstanceName = 'Distribute Reports' -- Filter StepInstance by
> relevant step, because there're multiple step instances for each report
> instance
>
>
> -- Clear the temporary table
> DROP TABLE #PrintableReports
>
> GO
>
> IF OBJECT_ID('dbo.SPusrReportsPrintDistributed') IS NOT NULL
> PRINT '<<< CREATED PROCEDURE dbo.SPusrReportsPrintDistributed >>>'
> ELSE
> PRINT '<<< FAILED CREATING PROCEDURE
> dbo.SPusrReportsPrintDistributed >>>'
> GO
>
> GRANT EXECUTE ON dbo.SPusrReportsPrintDistributed TO CRPConsoleUser,
> CRPConsoleUserFullAccess, CRPConsoleUserScanOnly
> GO |
|
| Back to top |
|
| |
External
Since: Jul 12, 2006
Posts: 13
|
(Msg. 3) Posted: Thu Dec 28, 2006 1:31 pm
Post subject: Re: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jul 12, 2006
Posts: 13
|
(Msg. 4) Posted: Thu Dec 28, 2006 1:41 pm
Post subject: Re: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jul 12, 2006
Posts: 13
|
(Msg. 5) Posted: Thu Dec 28, 2006 1:42 pm
Post subject: Re: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jul 25, 2006
Posts: 8
|
(Msg. 6) Posted: Thu Dec 28, 2006 4:13 pm
Post subject: Re: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
When you see an error like this look for all GROUP BY clauses, and verify
that the items after the GROUP BY are the only items in the SELECT of the
query using the GROUP BY that are not inside aggregate functions (max, min,
sum, etc).
In your case, DistributionCommonInstanceID is not part of the aggregate
created by the GROUP BY on DistributionPrintReceiptInstanceID. ReceiptTime
is exempt because it is inside a MAX. You will either need to GROUP BY
DistributionCommonInstanceID, SELECT DistributionPrintReceiptInstanceID
instead of DistributionCommonInstanceID or design your subquery.
LEFT JOIN
(
SELECT DistributionCommonInstanceID, MAX (ReceiptTime) MaxReceiptTime
FROM DistributionPrintReceiptInstance
GROUP BY DistributionPrintReceiptInstanceID
) mpri
Ben
"Emily" wrote in message
> Hi,
>
> I have a stored procedure and when I execute it, I always get the
> following error:
>
> "Column 'DistributionPrintReceiptInstance.DistributionCommonInstanceID'
> is invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
>
> Could anyone tell me how to get this fixed in the following stored
> procedure:
>
> -----------------------------------------------------------------------------------------------------------------------------
> CREATE Procedure dbo.SPusrReportsPrintDistributed
> AS
>
> IF OBJECT_ID('tempdb..#PrintableReports') IS NOT NULL
> DROP TABLE #PrintableReports
>
> CREATE TABLE #PrintableReports (
> AccountNumber int,
> MailingName varchar(40),
> Full_Name varchar(80),
> Location varchar(80),
> AccountName varchar(80),
> Rn_Descriptor varchar(80)
> )
>
> INSERT INTO #PrintableReports
> (
> AccountNumber,
> MailingName,
> Full_Name,
> Location,
> AccountName,
> Rn_Descriptor
> )
> EXEC PIVOTALVIEW...SPcrpGetReportsPrintDistributed
>
>
> SELECT TOP 65536 -- "65536" so we don't accidentally overwhelm
> the caller UI (flex-grid specifically)
> AccountNumber,
> MailingName,
> Full_Name AS 'Client Name',
> Location,
> AccountName,
> ri.ReportInstanceID,
> CASE WHEN (ri.Status LIKE '%COMPLETE%' AND
> (pri.ReceiptTime <> '' AND pri.ReceiptTime <= GetDate())) -- Scanned
> THEN 'Scanned'
> WHEN (ri.Status LIKE '%COMPLETE%' AND
> (pri.ReceiptTime = '')) -- Printed only (not scanned)
> THEN 'Printed'
> END AS 'Print Receipt Status',
> pri.ReceiptUserName,
> pri.ReceiptTime,
> dci.DistributionCommonInstanceID
>
> FROM #PrintableReports
> INNER JOIN ReportInstance ri
> ON #PrintableReports.AccountNumber = ri.ReportInstanceID
> INNER JOIN StepInstance si
> ON si.ReportInstanceID = ri.ReportInstanceID
> INNER JOIN DistributionInstance di
> ON si.StepInstanceID = di.StepInstanceID
> INNER JOIN DistributionCommonInstance dci
> ON di.DistributionInstanceID = dci.DistributionInstanceID
>
> -- Get the latest scann user and time
> LEFT JOIN
> ( SELECT DistributionCommonInstanceID,
> MAX (ReceiptTime) MaxReceiptTime
> FROM
> DistributionPrintReceiptInstance
> GROUP BY
> DistributionPrintReceiptInstanceID
> ) mpri
> ON mpri.DistributionCommonInstanceID =
> dci.DistributionCommonInstanceID
>
> LEFT JOIN DistributionPrintReceiptInstance pri
> ON pri.DistributionCommonInstanceID =
> mpri.DistributionCommonInstanceID
> and pri.ReceiptTime = mpri.MaxReceiptTime
>
>
> WHERE
> si.StepInstanceName = 'Distribute Reports' -- Filter StepInstance by
> relevant step, because there're multiple step instances for each report
> instance
>
>
> -- Clear the temporary table
> DROP TABLE #PrintableReports
>
> GO
>
> IF OBJECT_ID('dbo.SPusrReportsPrintDistributed') IS NOT NULL
> PRINT '<<< CREATED PROCEDURE dbo.SPusrReportsPrintDistributed >>>'
> ELSE
> PRINT '<<< FAILED CREATING PROCEDURE
> dbo.SPusrReportsPrintDistributed >>>'
> GO
>
> GRANT EXECUTE ON dbo.SPusrReportsPrintDistributed TO CRPConsoleUser,
> CRPConsoleUserFullAccess, CRPConsoleUserScanOnly
> GO
> >> Stay informed about: Error: is invalid in the select list because it is not con.. |
|
| Back to top |
|
| |
|
Aaron Bertrand [SQL Serve
|
External
Since: Jan 10, 2008
Posts: 2166
|
(Msg. 7) Posted: Thu Dec 28, 2006 4:14 pm
Post subject: Re: Error: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
SELECT DistributionCommonInstanceID,
MAX (ReceiptTime) MaxReceiptTime
FROM
DistributionPrintReceiptInstance
GROUP BY
DistributionPrintReceiptInstanceID
Your statement above, used in a derived table below, is incorrect. You need
to decide if you want to return and group by DistributionCommonInstanceID or
DistributionPrintReceiptInstanceID. You can't return one and group by the
other.
"Emily" wrote in message
> Hi,
>
> I have a stored procedure and when I execute it, I always get the
> following error:
>
> "Column 'DistributionPrintReceiptInstance.DistributionCommonInstanceID'
> is invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
>
> Could anyone tell me how to get this fixed in the following stored
> procedure:
>
> -----------------------------------------------------------------------------------------------------------------------------
> CREATE Procedure dbo.SPusrReportsPrintDistributed
> AS
>
> IF OBJECT_ID('tempdb..#PrintableReports') IS NOT NULL
> DROP TABLE #PrintableReports
>
> CREATE TABLE #PrintableReports (
> AccountNumber int,
> MailingName varchar(40),
> Full_Name varchar(80),
> Location varchar(80),
> AccountName varchar(80),
> Rn_Descriptor varchar(80)
> )
>
> INSERT INTO #PrintableReports
> (
> AccountNumber,
> MailingName,
> Full_Name,
> Location,
> AccountName,
> Rn_Descriptor
> )
> EXEC PIVOTALVIEW...SPcrpGetReportsPrintDistributed
>
>
> SELECT TOP 65536 -- "65536" so we don't accidentally overwhelm
> the caller UI (flex-grid specifically)
> AccountNumber,
> MailingName,
> Full_Name AS 'Client Name',
> Location,
> AccountName,
> ri.ReportInstanceID,
> CASE WHEN (ri.Status LIKE '%COMPLETE%' AND
> (pri.ReceiptTime <> '' AND pri.ReceiptTime <= GetDate())) -- Scanned
> THEN 'Scanned'
> WHEN (ri.Status LIKE '%COMPLETE%' AND
> (pri.ReceiptTime = '')) -- Printed only (not scanned)
> THEN 'Printed'
> END AS 'Print Receipt Status',
> pri.ReceiptUserName,
> pri.ReceiptTime,
> dci.DistributionCommonInstanceID
>
> FROM #PrintableReports
> INNER JOIN ReportInstance ri
> ON #PrintableReports.AccountNumber = ri.ReportInstanceID
> INNER JOIN StepInstance si
> ON si.ReportInstanceID = ri.ReportInstanceID
> INNER JOIN DistributionInstance di
> ON si.StepInstanceID = di.StepInstanceID
> INNER JOIN DistributionCommonInstance dci
> ON di.DistributionInstanceID = dci.DistributionInstanceID
>
> -- Get the latest scann user and time
> LEFT JOIN
> ( SELECT DistributionCommonInstanceID,
> MAX (ReceiptTime) MaxReceiptTime
> FROM
> DistributionPrintReceiptInstance
> GROUP BY
> DistributionPrintReceiptInstanceID
> ) mpri
> ON mpri.DistributionCommonInstanceID =
> dci.DistributionCommonInstanceID
>
> LEFT JOIN DistributionPrintReceiptInstance pri
> ON pri.DistributionCommonInstanceID =
> mpri.DistributionCommonInstanceID
> and pri.ReceiptTime = mpri.MaxReceiptTime
>
>
> WHERE
> si.StepInstanceName = 'Distribute Reports' -- Filter StepInstance by
> relevant step, because there're multiple step instances for each report
> instance
>
>
> -- Clear the temporary table
> DROP TABLE #PrintableReports
>
> GO
>
> IF OBJECT_ID('dbo.SPusrReportsPrintDistributed') IS NOT NULL
> PRINT '<<< CREATED PROCEDURE dbo.SPusrReportsPrintDistributed >>>'
> ELSE
> PRINT '<<< FAILED CREATING PROCEDURE
> dbo.SPusrReportsPrintDistributed >>>'
> GO
>
> GRANT EXECUTE ON dbo.SPusrReportsPrintDistributed TO CRPConsoleUser,
> CRPConsoleUserFullAccess, CRPConsoleUserScanOnly
> GO
> >> Stay informed about: Error: is invalid in the select list because it is not con.. |
|
| Back to top |
|
| |
| Related Topics: | Column X is invalid in the select list ...not contained in.. -
column alias defined in select list and used in same selec.. - I want to define a column alias and then I want to use it in the same SELECT statement. I know it can't be done... is there a workaround? This is the Select statement that I'd like to reuse. Someone suggested a derived table and/or a view. This SELEC...
query perfomance issue with Select statment in the Select .. - Hi all, Using SQL 2000 sp4 This query works, put takes about 11 to 20 seconds. Just trying to be more effient with it and maybe write it better. I hope I explain this correctly so you guys can understnd. --Orders table has 869,000 rows --Table..
Invalid Column Error - Hi, Can anybody point me why I get the error "Invalid Column Name - emp" error. Thanks. Table structures are: Tree create table tree (emp Char(10) Not Null, Boss Char(10)); Stack CREATE TABLE Stack (Stack_top INTEGER Not Null, emp Char(1...
Invalid column name error missing? - Hi all, I have the following T-SQL script: USE tempdb BEGIN TRAN CREATE TABLE TABLE1(id int, ref int) CREATE TABLE TABLE2(id int) SELECT id FROM TABLE1 WHERE ref IN (SELECT ref FROM TABLE2) DROP TABLE TABLE1 DROP TABLE TABLE2 COMMIT TRAN GO I.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
