Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 17th, 2004, 03:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error message is wrong

This SQL produces the following error:
  Column 'vProcMgrLineSpeedPerWOID_Sub_Rev.AcualParameterVa lue' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Even though I do have it in the Group By clause.

CREATE PROCEDURE "spDataValidation_LineSpeed_ActualVsReported_LSYie ldMultiplier"
/*
[Forms]![F-DataValidation]!@BeginDate AND [Forms]![F-DataValidation]!@EndDate))
[Enter Percentage Limit]
*/
(
@BeginDate nvarchar(20),
@EndDate nvarchar(20),
@PercentageLimit int
)

AS
SELECT [T_SetupSheetHistoryProduction].WONum,
      [T_SetupSheetHistoryProduction].WOIDSub,
      [T_SetupSheetHistoryProduction].Revision,
      Min([T_SetupSheetHistoryProduction].Date) AS EarliestProd,
      [T_SetupSheetHistoryProduction].ComboCustPartNum AS Item,
      [T_SetupSheetHistoryCombinationsDetail].ComboLengthActual AS Length,
      Sum([T_SetupSheetHistoryProduction].QtyGood) AS TotalGoodQty,
      Sum([T_SetupSheetHistoryProduction].TotalRun) AS RunMins,
      CONVERT(float,AcualParameterValue) AS LineSpdRprt,
      CONVERT(float,AcualParameterValue)*([LSYieldMult]) AS ReportedYld,
      (Sum([QtyGood])/Sum([TotalRun])*[ComboLengthActual]) AS CalcLineSpd,
      ((((CONVERT(float,[AcualParameterValue])*([LSYieldMult]))-((Sum([QtyGood])/Sum([TotalRun]))*[ComboLengthActual]))/(CONVERT(float,[AcualParameterValue]))*100)) AS Diff,
      Count([T_SetupSheetHistoryProduction].ProdID) AS Entries
FROM (([T_SetupSheetHistoryProduction]
INNER JOIN [T_SetupSheetHistoryCombinationsDetail]
     ON ([T_SetupSheetHistoryProduction].WOIDSub = [T_SetupSheetHistoryCombinationsDetail].WOIDSub)
     AND ([T_SetupSheetHistoryProduction].ComboCustPartNum = [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum)
     AND ([T_SetupSheetHistoryProduction].ECN = [T_SetupSheetHistoryCombinationsDetail].ECN)
     AND ([T_SetupSheetHistoryProduction].WONum = [T_SetupSheetHistoryCombinationsDetail].WOID)
     AND ([T_SetupSheetHistoryProduction].QuoteID = [T_SetupSheetHistoryCombinationsDetail].QuoteID))
INNER JOIN vProcMgrLineSpeedPerWOID_Sub_Rev
     ON ([T_SetupSheetHistoryProduction].WOIDSub = vProcMgrLineSpeedPerWOID_Sub_Rev.WOIDSub)
     AND ([T_SetupSheetHistoryProduction].Revision = vProcMgrLineSpeedPerWOID_Sub_Rev.Revision)
     AND ([T_SetupSheetHistoryProduction].ECN = vProcMgrLineSpeedPerWOID_Sub_Rev.ECN)
     AND ([T_SetupSheetHistoryProduction].QuoteID = vProcMgrLineSpeedPerWOID_Sub_Rev.QuoteID)
     AND ([T_SetupSheetHistoryProduction].WONum = vProcMgrLineSpeedPerWOID_Sub_Rev.WOID))
INNER JOIN [T_SetupSheetHistoryHeader]
     ON ([T_SetupSheetHistoryProduction].Revision = [T_SetupSheetHistoryHeader].Revision)
     AND ([T_SetupSheetHistoryProduction].ECN = [T_SetupSheetHistoryHeader].ECN)
     AND ([T_SetupSheetHistoryProduction].WOIDSub = [T_SetupSheetHistoryHeader].WOIDSub)
     AND ([T_SetupSheetHistoryProduction].WONum = [T_SetupSheetHistoryHeader].WOID)
     AND ([T_SetupSheetHistoryProduction].QuoteID = [T_SetupSheetHistoryHeader].QuoteID)
WHERE (((vProcMgrLineSpeedPerWOID_Sub_Rev.AcualParameter Value) Is Not Null)
     AND (([T_SetupSheetHistoryProduction].Date) Between @BeginDate AND @EndDate))
GROUP BY [T_SetupSheetHistoryProduction].WONum,
      [T_SetupSheetHistoryProduction].WOIDSub,
      [T_SetupSheetHistoryProduction].Revision,
      [T_SetupSheetHistoryProduction].ComboCustPartNum,
      [T_SetupSheetHistoryCombinationsDetail].ComboLengthActual,
      CONVERT(float,AcualParameterValue),
      CONVERT(float,AcualParameterValue)*([LSYieldMult])
HAVING Sum([T_SetupSheetHistoryProduction].QtyGood)>0
     AND Sum([T_SetupSheetHistoryProduction].QtyGood) Is Not Null
     AND Sum([T_SetupSheetHistoryProduction].TotalRun)>0
     AND Sum([T_SetupSheetHistoryProduction].TotalRun) Is Not Null
     AND ((Abs(CONVERT(float,(((CONVERT(float,[AcualParameterValue])*([LSYieldMult]))-((Sum([QtyGood])/Sum([TotalRun]))*[ComboLengthActual]))/(CONVERT(float,[AcualParameterValue]))*100))))>CONVERT(float,@PercentageLimit))
__________________
Mitch
 
Old March 17th, 2004, 03:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Did you try using the new alias names in the group by clause? I don't know if that's it...
 
Old March 17th, 2004, 04:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't think you can do that, it gives an error.
 
Old March 17th, 2004, 07:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Alias names won't work - they don't come into being until the very end of the SELECT statement processing - just before the ORDER BY clause is processed. That's why you have to repeat the column expressions in the GROUP BY and HAVING (and WHERE) clauses.

It sure looks like your code should work.

There isn't something funny going on in (I assume) the view 'vProcMgrLineSpeedPerWOID_Sub_Rev' - is there grouping going on in there, maybe that's causing the error?

P.S. Learn to make use of table aliases - you could save yourself a *lot* of typing and us a lot of reading given your looonnnggg table names :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
No such file or directory error for wrong linking awotta BOOK: Professional Assembly Language 1 January 10th, 2010 07:44 AM
Error in script but can't find what's wrong!! BramuS ASP.NET 1.0 and 1.1 Basics 1 May 10th, 2005 04:32 AM
ADODB.Recordset Error on wrong line???? Greywacke Classic ASP Databases 4 February 23rd, 2004 11:01 AM
email message with wrong characters marcrock Flash (all versions) 4 February 8th, 2004 05:35 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.