sub query within the SUM aggregate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sub query within the SUM aggregate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-03 : 08:03:13

M writes "I am trying to replace some query code in sql server.

the original code is
qty_avail = SUM(CASE WHEN locationid NOT IN (''00R0T'', ''00R0W'', ''00R0K'') THEN qty ELSE 0 END),

and I am trying to replace with

qty_avail = SUM(CASE WHEN locationid NOT IN (SELECT LocationID FROM ForecastExcludeLocations (nolock)) THEN qty ELSE 0 END),

I get the following msg
Server: Msg 130, Level 15, State 1, Line 184
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Server: Msg 156, Level 15, State 1, Line 185
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 186
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Line 187
Incorrect syntax near the keyword 'THEN'.
Server: Msg 170, Level 15, State 1, Line 201
Line 201: Incorrect syntax near 'innerdata'.

I can I replace this code to look into a table instead of hardcoding values?

Thanks"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-03 : 08:24:41

post same sample data.. so that we can understand your issue..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-03 : 10:53:11

As the error displays, you cant use subquery when you use SUM function. Use Join and filter the records

Select sum(qty) from yourtable T
where not exists(select * from ForecastExcludeLocations where LocationID =T.LocationID )

Madhivanan

Failing to plan is Planning to fail

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:22:14

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 06:24:45

quote:
Originally posted by shijobaby

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Why are you always posting solutions to old thread?
Cant you see new threads?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources