Tags: aggregate, column, contained, error, execute, following, invalid, procedure, programming, select, sql, stored

Error: is invalid in the select list because it is not contained in either an aggregate fu

On Programmer » SQL

16,432 words with 6 Comments; publish: Thu, 29 May 2008 00:19:00 GMT; (200109.38, « »)

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

All Comments

Leave a comment...

  • 6 Comments
    • 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

      #1; Thu, 29 May 2008 00:21:00 GMT
    • 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" <fir5tsight.sql.todaysummary.com.yahoo.com> wrote in message

      news:1167339942.628568.303280.sql.todaysummary.com.a3g2000cwd.googlegroups.com...

      > 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

      >

      #2; Thu, 29 May 2008 00:22:00 GMT
    • 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" <fir5tsight.sql.todaysummary.com.yahoo.com> wrote in message

      news:1167339942.628568.303280.sql.todaysummary.com.a3g2000cwd.googlegroups.com...

      > 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

      >

      #3; Thu, 29 May 2008 00:23:00 GMT
    • Hi Payson,

      Many thanks for the very specific advice! It solved my problem.

      -Emily

      #4; Thu, 29 May 2008 00:24:00 GMT
    • Hi Ben,

      Thanks for the advice! I changed it to

      GROUP BY DistributionCommonInstanceI

      and it works!

      -Emily

      #5; Thu, 29 May 2008 00:25:00 GMT
    • Hi Aaron,

      I changed it to

      GROUP BY DistributionCommonInstanceID

      and it works!

      Many thanks!

      -Emily

      #6; Thu, 29 May 2008 00:26:00 GMT