Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What does this error message mean?

Posted on 2010-11-30
7
Medium Priority
?
284 Views
Last Modified: 2012-05-10
Getting an outer reference error on a group by expression
Error.doc
0
Comment
Question by:SPLady
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34239921
Please also attach the SQL statement.  (The SQL statement is truncated in the error message text. ;-)
0
 
LVL 1

Author Comment

by:SPLady
ID: 34240032
thank you @ 8080 Diver the code is attached
SELECT DISTINCT 
                      (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, 
                      - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, vReportData.salesOrderLine, AVG(PlanJoin.[Plan]) AS Expr1, 
                      vReportData.EnergyOrg, vReportData.transyear, vReportData.subProduct, PlanJoin.recType, PlanJoin.Month, PlanJoin.Year, 
                      vReportData.lineValue AS Expr2, vReportData.transDate
FROM         vReportData INNER JOIN
                      PlanJoin ON vReportData.recType = PlanJoin.recType AND vReportData.EnergyOrg = PlanJoin.EnergyOrg AND 
                      vReportData.transyear = PlanJoin.Year AND MONTH(vReportData.transDate) = PlanJoin.Month
GROUP BY (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, 
                      - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END, vReportData.salesOrderLine, vReportData.EnergyOrg, vReportData.transyear, 
                      vReportData.subProduct, PlanJoin.recType, PlanJoin.Month, PlanJoin.Year, vReportData.lineValue, vReportData.transDate
HAVING      (vReportData.transDate > CONVERT(DATETIME, '2010-11-15 00:00:00', 102))

Open in new window

0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 1000 total points
ID: 34240118
Frankly, I would be tempted to create a SELECT query that selects the data without the AVG (and just the PlanJoin.[Plan], instead) and then apply the AVG in an outer select so that the GROUP BY is a far simpler clause.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:SPLady
ID: 34243283
Thank you @8080 driver, can you show me an example of that query?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34243397
I think that thie following should work.  (Can't test it because I don't have the database. ;-)
SELECT  DISTINCT 
        WorkingDays
       ,salesOrderLine
       ,AVG(PlanJoin.[Plan]) AS AvgPlan
       ,EnergyOrg
       ,transyear
       ,subProduct
       ,recType
       ,Month
       ,Year
       ,lineValue
       ,transDate
FROM
(
 SELECT DATEDIFF(DD, @start, @end) + 1) - 
        (DATEDIFF(DD, DATEADD(DD, 6 - (DATEPART(DW, @start) + 
         @@DATEFIRST - 2) % 7, @start), 
         DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) / 7 * 2 -
         CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 
              WHEN 7
              THEN 1
              ELSE 2
         END -
         CASE (DATEPART(DW, @end) + @@DATEFIRST - 2) % 7 + 1 WHEN 6
              THEN 1                                       WHEN 7
              THEN 2
              ELSE 0
         END AS WorkingDays,
       vReportData.salesOrderLine,
       PlanJoin.[Plan],
       vReportData.EnergyOrg,
       vReportData.transyear,
       vReportData.subProduct,
       PlanJoin.recType,
       PlanJoin.Month,
       PlanJoin.Year,
       vReportData.lineValue,
       vReportData.transDate
FROM   vReportData
INNER JOIN PlanJoin
ON     vReportData.recType = PlanJoin.recType
       AND vReportData.EnergyOrg = PlanJoin.EnergyOrg
       AND vReportData.transyear = PlanJoin.Year
       AND MONTH(vReportData.transDate) = PlanJoin.Month
WHERE vReportData.transDate > '2010-11-15 00:00:00'
) Z
GROUP BY WorkingDays,
       salesOrderLine,
       EnergyOrg,
       transyear,
       subProduct,
       recType,
       Month,
       Year,
       lineValue,
       transDate;

Open in new window

0
 
LVL 1

Author Comment

by:SPLady
ID: 34243522
what is an outer reference?
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1000 total points
ID: 34243941
I am pretty sure that it has something to do with the case statement in you GROUP BY clause.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question