• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

What does this error message mean?

Getting an outer reference error on a group by expression
Error.doc
0
SPLady
Asked:
SPLady
  • 4
  • 3
2 Solutions
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now