Aggregate error on UPDATE

I have the UPDATE command below that parses OK but when I execute it I get 
"An aggregate may not appear in the WHERE clause unless it is in a subquery 
contained in a HAVING clause or a select list, and the column being 
aggregated is an outer reference."  I am trying to update a date in a table 
using results of 3 joined tables and limiting it to only those with MAX date 
< 2010.  Can anyone tell me how to correct this TSQL?  Thanks.

UPDATE dbo.Branches 
SET ServiceEndDate = MAX(P.PeriodEnding)
FROM dbo.PayChecks P INNER JOIN
     dbo.PeopleLink L ON P.PeopleLinkID = L.PeopleLinkID INNER JOIN
     dbo.Branches B ON L.Branch = B.Branch
WHERE (MAX(P.PeriodEnding) < CONVERT(DATETIME, '2010-01-01 00:00:00', 102))

-- 
David
0
Utf
3/28/2010 3:21:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
844 Views

Similar Articles

[PageSpeed] 7

As the error message states you cannot use the aggregate function in the WHERE clause, you have to use it in the HAVING 
clause. Try this:

UPDATE dbo.Branches
SET ServiceEndDate = (SELECT MAX(P.PeriodEnding)
                       FROM dbo.PayChecks AS P
                       INNER JOIN dbo.PeopleLink AS L
                          ON P.PeopleLinkID = L.PeopleLinkID
                       WHERE L.Branch = dbo.Branches.Branch
                       HAVING MAX(P.PeriodEnding) < '20100101')
WHERE EXISTS(SELECT *
              FROM dbo.PayChecks AS P
              INNER JOIN dbo.PeopleLink AS L
                 ON P.PeopleLinkID = L.PeopleLinkID
              WHERE L.Branch = dbo.Branches.Branch
              HAVING MAX(P.PeriodEnding) < '20100101');

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/28/2010 3:41:15 PM
Would you mind posting DDL in the future, so people do not have to
guess about keys, constraints and the meaning of vague data element
names? That is basic Netiquette on SQL newsgroups.

Let's clean the data element names (did you really model the physical
paychecks instead of the payroll!? "PeopleLink" sounds like sausage
for Jeffrey Lionel Dahmer; RDBMS uses references and not links. And
ISO-11179 uses one property component in a name -- it is a _link or an
_id but not both by definition. Tables are sets, which will have
collective or plural names.

I think this might be what you want.

UPDATE Branches
   SET service_end_date
       = (SELECT MAX(P.period_end_date)
            FROM Payroll AS P,
                 Personnel AS E,
                 Branches AS B
           WHERE P.emp_id = L.emp_id
             AND E.branch_name = B.branch_name
          HAVING MAX(P.period_end_date)
                 < CAST ('2010-01-01' AS DATE));

Use the ANSI/ISO CAST() and DATE data type and not the proprietary
string function CONVERT() for temporal data.

A HAVING clause without a GROUP BY will treat the entire result as a
group and work on it. I hope you don't mind NULLs for empty result
sets. If not, you need more code to give a default.
0
CELKO
3/28/2010 6:30:22 PM
>                 < CAST ('2010-01-01' AS DATE));

Your at it again - at what level do you accept other professionals may 
actually know better and more than you on a given product in this case me 
(an expert in the profession with some 23 years behind me)?

YYYY-MM-DD for the pecabyth time is not consistent in SQL Server.

That is a display friendly format (those are words from the ISO standard not 
mine).

YYYYMMDD is consistent and works across all major RDBMS so is both 
consistent, portable and ISO compliant.

Instead of killing people be consistent with dates and give those folks 
outside of Texas (the obvious centre of the universe) a break.

Will you ever listen? errr... no.

--ROGGIE--


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:c50b9e3c-f492-4a93-a0bf-f45f55e87bc6@e7g2000yqf.googlegroups.com...
> Would you mind posting DDL in the future, so people do not have to
> guess about keys, constraints and the meaning of vague data element
> names? That is basic Netiquette on SQL newsgroups.
>
> Let's clean the data element names (did you really model the physical
> paychecks instead of the payroll!? "PeopleLink" sounds like sausage
> for Jeffrey Lionel Dahmer; RDBMS uses references and not links. And
> ISO-11179 uses one property component in a name -- it is a _link or an
> _id but not both by definition. Tables are sets, which will have
> collective or plural names.
>
> I think this might be what you want.
>
> UPDATE Branches
>   SET service_end_date
>       = (SELECT MAX(P.period_end_date)
>            FROM Payroll AS P,
>                 Personnel AS E,
>                 Branches AS B
>           WHERE P.emp_id = L.emp_id
>             AND E.branch_name = B.branch_name
>          HAVING MAX(P.period_end_date)
>                 < CAST ('2010-01-01' AS DATE));
>
> Use the ANSI/ISO CAST() and DATE data type and not the proprietary
> string function CONVERT() for temporal data.
>
> A HAVING clause without a GROUP BY will treat the entire result as a
> group and work on it. I hope you don't mind NULLs for empty result
> sets. If not, you need more code to give a default. 

0
Tony
3/28/2010 7:38:34 PM
>
> UPDATE Branches
>     SET service_end_date
>         = (SELECT MAX(P.period_end_date)
>              FROM Payroll AS P,
>                   Personnel AS E,
>                   Branches AS B
>             WHERE P.emp_id = L.emp_id
>               AND E.branch_name = B.branch_name
>            HAVING MAX(P.period_end_date)
>                   <  CAST ('2010-01-01' AS DATE));
>

Am I reading this wrong, or will it update every service end date, no 
matter which branch, with the same value?  The OP's code appears to want 
to update each branch with its own service end date.

Joe De Moor
0
J
3/29/2010 7:55:47 PM
Reply:

Similar Artilces:

recieved error message 0x8DE00006
I cannot get Outlook to send a mass mailer out to business emails. Is ther a max amount that I can send too? I just keep getting this error message and then I go to look it up and there is not solution. Please help!? I have about 375 emails addresses in a distribution list. I just can't seem to send it out. This is most probably an issue with your ISP. They put a quota on how many emails you can send out at a single time. It is a control put in place to restrict Spam. Call your ISP for info -- Peter Please Reply to Newsgroup for the benefit of others Requests fo...

"Application has failed to start..." error message with VS2005 on new machine
VC++ MFC Project working absolutely fine on one machine Installed Visual Studio on a new portable and copied the project and all the settings over, done a complete rebuild which worked fine but now get an error message "The application has failed to start becuase the application configuration is incorrect. Reinstalling the application may fix this problem" Have tried reinsalling (but I dont think this is relevant as I am working with VStudio) the message pops up a couple of times but then the program runs OK Does anyone know what this means and how to find out what is missin...

Cannot show, import, export userform: error &H80004005 / component is not corretly installed
Please, help me! I can not find where is the the information I need. My problem is: I had Windows 2000 and Office 2000. Now they installed Windows XP and Office 2000 (serv.pack 3). And my forms do not work anymore! The error message says nothing useful, only: 1) the component is not corretly installed (ok! but it's a common form with only normal windows common buttons and labels, not a single userparty creative control) 2) error: &H80004005 -2147467259 3) memory insuficient (ahah, at 512 Mb, only Excel running... ) 4) can not set OleObjectBlob property But if I try to open the...

All macros failing part way through with different error messages
All the macros I have in various workbooks are failing - usually part-way through - with different error messages each time. When I check the debugger and try to edit the text, there seems no reason why there should be a problem. The macro has so far stopped on instructions to : - open a workbook - select a worksheet - paste a column in a different place Several times the macro has just stopped running without completing and without showing an error message. I cannot run a host of urgent daily reports without using the macros. Please help! NO way to help unless you tell us exactly...

VLookup in VBA giving error message
Hi, I'm having trouble making the vlookup function work in VBA. the values that are used are all dates, I don't know if that makes a difference. Worksheets("sheet1").range("a1").value = application.WorksheetFunction. _ Vlookup(Range("c1"), range("a:a"), 2) this gives me a run-time error '1004' Unable to get the vlookup property of the Worksheet function class so i tried this: Worksheets("sheet1").range("a1").value = application.Vlookup(Range("c1"), _ range("a:a"), 2) but this gives me a #REF ...

Exchange Connector Wizard error
I received this error while trying to run this wizard for a generic CRM user(what role does it have to have?) that would accept all email directly to CRM. I ran it on a client computer (was this wrong?) Here is the error, anyway. "Failed to initialize the rule management subsystem." What is the correct procedure to run this wizard? I did not find it anywhere. Thanks! ...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Vb.net 2008 ContextMenuStrip logical error when running code
Greetings, I have a connectmenustrip item that when clicked runs the following code (see below) Now if the event is called by the button i.e. cmdDeleteingBooking.Click the linq query returns the appropriate value. However when called by cntMnuCancelBookingItem.Click is returns 0 even if a checkbox is of 'TRUE' value. Debugging shows the code runs exactly the same code (which loops around rows in a datagridview checking if the checkbox has been checked). Could someone explain the reasoning why the same code would return different results? Private Sub cmdDelete...

loan amortisatio chart not updating
Hi, I use MS Monet 2007 premium. I have created a loan amortisation account which breaks up my monthly instslment into pricipal and interest. The loan commencd from 7 October 2005 and is for a period of 5 years. The problem is that the loan account does ot show any loan instalments beyond 7 October 2006 (exactly 1 year after the commencement). Why is this happening. Why is the account not updating with instalments which have been debited to my account after 7 October 2007. Please help. Were you depending on downloaded transaction data for this account? Did the download link br...

Metadata Cache Error 04-14-04
When trying to "Go Offline" after synchronization completes I receive the: "Metadata Cache Error" No Entity Anyone experience/solve this issue? Hi, You may have replication setup issue on your SQL server, I suggest you to go through this article : http://mbs.microsoft.com/TechKnowledge/techknowledge.asp?print=true&id=33588&code=7842 If this is OK, you may test the following manipulation : Open SQL Enterprise Manager; go to Start select All Programs and then Microsoft SQL Server. Expand Microsoft SQL Servers, expand SQL Server Group, expand your Microsoft CR...

js error in ie8
Hi, i am opening a popup window in ie8, the popup is load with loaded with one url and some javascript classes are intialized and able to call the member functions are from page and then i am re-directing the page the another url and the js classes are reinitialized. when i try to call the member function of the js class in the re-directed page, ie8 throws js error as "Unexpected call to method or property access". When debug the code, i found that the class is properly initialized and able to access the member variables of the class. i am getting problem only ...

Label Error with image?
Hi, We are trying to print a label for jewelry, the only issue we seem to have is that when an image is put into the label designer the image is the right dimensions but instead of being an image it is a box with red lines cris-crossing with the word 'ERROR' displayed in the center. This all happens before printing, although when we try to print there is no image. So what is it that we are doing wrong to not have an image displayed in the Label designer of RMS PO Manager. Any help[ would be greatly appreciated! Thanks ___________________ Dave Dave, In Label Designer, you have ...

Time update as a limited user not working
I added time update permisssion to my limited user acct. but it does not work. When I try, the time synchonization is greyed out. How can I get it to work. Thanks. On Apr 4, 12:33=A0pm, Mint <chocolatemint77...@yahoo.com> wrote: > I added time update permisssion to my limited user acct. but it does > not work. > When I try, the time synchonization is greyed out. > > How can I get it to work. > > Thanks. Is this Windows MCE SP2? What method did you use to add time update permission to your limited user account? Does your unlimited user accou...

error on install to pocket pc
"window\mny2day.dll" is in use or is in ROM. IFthe file is not in ROM, please close the application using the file. I do not understand what file could be using the same application or how to check the ROM. ...

Frx Susbcript 9 error
I am running a consolidated Income Statement in FRx and I get an Error 9 when it hits this one company. It consolidates 5 different companies, one that is translated. I just can;t figure it out. I have a very similar Balance Sheet that runs just fine. I have tried everything, change the output names, etc. I can run the report if I just select that one company but when I run the total company it won't run, it stops with the error. I am using row linking and currency translation. I can run the report for all companies individually. I just don't get it. I have gone through the various kb...

Sql Agent Error
After installing CRM we are receiving the following error. "An error occurred when attempting to set the SQLSERVERAGENT service to AutoStart: System.ComponentModel.Win32Exception: The specified service does not exist as an installed service at Microsoft.Crm.Setup.Common.Utility.ServiceUtility.SetServiceToAutoStart(String serviceName, String machineName) at Microsoft.Crm.Setup.Server.ServiceRunningValidator.AutoStartLocalService(String serviceName, TimeSpan waitTimeForStart) We have CRM on a SBS 2003 machine and SQL EE 2000 on a W2K3 machine. Both are DCs the SBS holds all FS...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Excel Running Wild After Update
I ran the Office update via Auto Update. Word and Powerpoint seem fine but Excel attemtps to open every compatible file (and a few that aren't) when I start it up. After it overdoses on files it fails and closes. Any ideas on what to do other than reverting to an older version, pre-update? Wild guess: Look in Applications:Microsoft Office 2004:Office:Startup:Excel: . Are there a bunch of random files in there? I think Excel would try to open anything in there on launch. On 3/23/06 9:04 AM, "Jeff Fread" wrote: > I ran the Office update via Auto Update. Word and Powe...

IM error ADO field is nothing
I am trying to do an import for manual payroll checks. I have used the same set up as is used in the sample but I recieve the above error. It also says 0 integrations failed. I can't find anything in knowledgebase. Thanks for any help. Tracey D Open IM, select your integration, double-click on Mappings. Click on the Transactions collection, then click on the Options tab. You will want to make sure the Record Source option rule is set to Use Source Recordset and that the Source is set to your source query. If the above is defined properly, then you will want to make sure you a...

trial & error
How do I calculate the z factor automatically by trial & error EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com You keep asking about the z factor. What's the z factor? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ <khalid khalid> wrote in message news:200782610734gawarh@hotmail.com... > How do I calculate the z factor automatically by trial & error > > EggHeadCafe - .NET Developer Portal of Choice > http://www.eggheadcafe.com ...

Outlook reports OLE registration error
I have Office 2000 running in Windows Me on a Dell Dimension XPS D266 with 64 meg ram. Outlook stopped working so I ran Repair from Office 2000 CD and then Re- install Office from same source. Both times Outlook says" An OLE registration error occured. Program is not correctly installed. Run Setup again." Doesn't seem to be much sense in running it again. What now? Reinstall Outlook Express. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal acco...

Cdn Payroll July 1 errors
Since the tax update was installed for July 1 for Canadian Payroll, our Detail Range Reports do not have the proper data. I noticed that CPY30260 has not been updated!!! this is not good. I contacted our vendor to see if this is a known issue. There is a round2 tax update that does not affect our province so I have not installed it. I was waiting for round3 which has not been released yet. Is anyone else aware of this? We have not seen any errors during Update Masters. Our YTD amounts and T4 amounts appear to be correct. thanks. never mind "barbola" wrote: > Since the...

insert text to different pages and update
Is there a way to insert a text cell from one page put into another page and when the first page cell is updated the second page cell will also be updated. Using the = sign just displays the formula, not the text. :confused: Thanks Frank -- fwburkey ------------------------------------------------------------------------ fwburkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30314 View this thread: http://www.excelforum.com/showthread.php?threadid=505331 Not clear what you are doing wrong. the formula should look lik =Sheet1!A1 Make sure there is noth...

SMTP ERROR #11
I keep getting this error when sending email comcast.net , any ideas ? jxxx@comcast.net on 3/7/2006 4:23 PM There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <xx.domain.com #5.5.0 smtp;550-xx.xx.yy.zz blocked by ldap:ou=rblmx,dc=comcast,dc=net> 1- Check if your exchange server can resolve this domain name. 2- try using telnet to port 25 on that server and see if it connects. 3- check with ww.ordb.org they might be listed there. -- My Regards, Omar El-Sherif "tricky4" <tricky4@discussions...