Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

GROUP BY Error


Hello,  I'm new to SQL Server 2005 Express.  Well I've been given this mega
query a co-worker created in Access and they want me to make it work in SQL
Server.  I'm getting a "GROUP BY expression must contain at least one column
that is not an outer reference" error.  Any insight would be appreciated.  

I told you it was a mega query.

  SELECT
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblEmployee.OTRoster,
      tblOTRoster.OTRosterDescription,
      '' AS TenMileProj,
      '' AS TenMileProjDesc,
     (
         SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn] FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ) AS OTBal,
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName] AS Employee,
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '' AS AuxDate,
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs AS OTHrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster AS OTWrkRstr,
      tblOTRoster.OTRosterType
   FROM  (tblOTRoster
      INNER JOIN  (tblCostCtr
      INNER JOIN  (tblClassification
      INNER JOIN tblEmployee
      ON tblClassification.ClassID = tblEmployee.Class )
      ON tblCostCtr.CostCtr = tblEmployee.AsgnCC )
      ON tblOTRoster.OTRoster = tblEmployee.OTRoster )
      LEFT JOIN OneqryOTLast30Days
      ON tblEmployee.EmpID = [OneqryOTLast30Days].EmpID
   GROUP BY
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblEmployee.OTRoster,
      tblOTRoster.OTRosterDescription,
      '',
      '',
     (
         SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn] FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ),
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName],
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '',
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster,
      tblOTRoster.OTRosterType
   HAVING (((tblClassification.ClassBU) <> 22))
    UNION
   SELECT
      tblAuxList.AuxCostCtr,
      tblCostCtr.CostCtrName,
      tblAuxList.AuxOTRoster,
      tblOTRoster.OTRosterDescription,
      '' AS TenMileProj,
      '' AS TenMileProjDesc,
    (

        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        )AS OTBal,
      tblAuxList.EmpID,
      [LastName] + ', ' + [FirstName] AS Employee,
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '' AS AuxDate,
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster AS OTWrkRstr,
      tblOTRoster.OTRosterType
   FROM  (tblClassification
      INNER JOIN  (tblEmployee
      INNER JOIN  (tblCostCtr
      INNER JOIN  (tblOTRoster
      INNER JOIN tblAuxList
      ON tblOTRoster.OTRoster = tblAuxList.AuxOTRoster )
      ON tblCostCtr.CostCtr = tblAuxList.AuxCostCtr )
      ON tblEmployee.EmpID = tblAuxList.EmpID )
      ON tblClassification.ClassID = tblEmployee.Class )
      LEFT JOIN OneqryOTLast30Days
      ON (tblAuxList.AuxOTRoster = [OneqryOTLast30Days].[OTRoster]) AND
(tblAuxList.EmpID = [OneqryOTLast30Days].EmpID)
   GROUP BY
      tblAuxList.AuxCostCtr,
      tblCostCtr.CostCtrName,
      tblAuxList.AuxOTRoster,
      tblOTRoster.OTRosterDescription,
      '',
      '',
    (
        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ),
      tblAuxList.EmpID,
      [LastName] + ', ' + [FirstName],
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '',
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].Hrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      [OneqryOTLast30Days].OTRoster,
      tblOTRoster.OTRosterType
    UNION
   SELECT
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblTenMileRule.MainProject,
      tblOTRoster.OTRosterDescription AS [tblOTRoster_OTRosterDescription],
      tblTenMileRule.Within10Miles,
      tblOTRoster_1.OTRosterDescription AS [tblOTRoster_1_OTRosterDescription]
,
    (
        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ) AS OTBal,
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName] AS Employee,
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '' AS AuxDate,
      [OneqryOTLast30Days].OTDateOP,
      Sum([OneqryOTLast30Days].Hrs) AS SumOfHrs,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      tblOTRoster_1.OTRoster AS OTWrkRstr,
      [OneqryOTLast30Days].OTRosterType
   FROM  ( (tblCostCtr
      INNER JOIN  (tblClassification
      INNER JOIN  (tblOTRoster  AS tblOTRoster_1
      INNER JOIN  ( (tblOTRoster
      INNER JOIN tblTenMileRule
      ON tblOTRoster.OTRoster = tblTenMileRule.MainProject )
      INNER JOIN tblEmployee
      ON tblTenMileRule.Within10Miles = tblEmployee.OTRoster )
      ON tblOTRoster_1.OTRoster = tblTenMileRule.Within10Miles )
      ON tblClassification.ClassID = tblEmployee.Class )
      ON tblCostCtr.CostCtr = tblEmployee.AsgnCC )
      LEFT JOIN OneqryOTLast30Days
      ON tblEmployee.EmpID = [OneqryOTLast30Days].EmpID )
      LEFT JOIN tblEvent
      ON [OneqryOTLast30Days].EventID = tblEvent.OTEventID
   GROUP BY
      tblEmployee.AsgnCC,
      tblCostCtr.CostCtrName,
      tblTenMileRule.MainProject,
      tblOTRoster.OTRosterDescription,
      tblTenMileRule.Within10Miles,
     (
        SELECT Sum(ISNULL(OTHours,0)) + [OTHrsIn]  FROM tblOvertime WHERE
tblOvertime.EmpID = tblEmployee.EmpID
        ),
      tblEmployee.EmpID,
      [LastName] + ', ' + [FirstName],
      tblEmployee.Class,
      tblClassification.ClassBU,
      tblClassification.ClassShortNME,
      tblEmployee.SenCredits,
      '',
      [OneqryOTLast30Days].OTDateOP,
      [OneqryOTLast30Days].OTDate,
      [OneqryOTLast30Days].OTOp,
      tblOTRoster_1.OTRoster,
      [OneqryOTLast30Days].OTRosterType

--
Message posted via http://www.sqlmonster.com

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc