Solved

What does this error message mean?

Posted on 2010-11-30
7
281 Views
Last Modified: 2012-05-10
Getting an outer reference error on a group by expression
Error.doc
0
Comment
Question by:SPLady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 250 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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 250 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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