What does this error message mean?

Getting an outer reference error on a group by expression
Error.doc
LVL 1
SPLadyAsked:
Who is Participating?
 
8080_DiverCommented:
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
 
8080_DiverCommented:
Please also attach the SQL statement.  (The SQL statement is truncated in the error message text. ;-)
0
 
SPLadyAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SPLadyAuthor Commented:
Thank you @8080 driver, can you show me an example of that query?
0
 
8080_DiverCommented:
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
 
SPLadyAuthor Commented:
what is an outer reference?
0
 
8080_DiverCommented:
I am pretty sure that it has something to do with the case statement in you GROUP BY clause.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.