SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 I need to combine data-retrieve with variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eurob
Posting Yak Master

USA
100 Posts

Posted - 08/31/2005 :  12:38:57  Show Profile  Visit eurob's Homepage  Send eurob an ICQ Message  Reply with Quote
declare @a int
select clientid,@a from mytable

I get the error : "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

How can be worked around this issue ?


Thanks




robert

SamC
White Water Yakist

USA
3467 Posts

Posted - 08/31/2005 :  14:30:20  Show Profile  Reply with Quote
SELECT @a = ClientID FROM MyTable

Edited by - SamC on 08/31/2005 14:30:35
Go to Top of Page

eurob
Posting Yak Master

USA
100 Posts

Posted - 08/31/2005 :  14:59:45  Show Profile  Visit eurob's Homepage  Send eurob an ICQ Message  Reply with Quote
sorry,

i forgot something

declare @a int
select clientid,@a = sum(items) from mytable

I get the error : "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

robert
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 08/31/2005 :  16:01:55  Show Profile  Reply with Quote
Gotta use two steps. It makes sense.

SELECT @a = sum(items) from MyTable

SELECT ClientID FROM MyTable

Take a look and think about why doing both in a single step is a conflict.
Go to Top of Page

eurob
Posting Yak Master

USA
100 Posts

Posted - 09/01/2005 :  07:53:55  Show Profile  Visit eurob's Homepage  Send eurob an ICQ Message  Reply with Quote
But I need the sum grouped by client, so I need to have it in one select.



robert
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/01/2005 :  08:43:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this

SELECT @a = sum(items) from MyTable where Clienid=someId


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

anandc
Starting Member

India
20 Posts

Posted - 09/01/2005 :  08:44:35  Show Profile  Reply with Quote
You may use the query like this...
SELECT clientid, SUM(items) as A FROM mytable

- Anand
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/01/2005 :  08:50:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by anandc

You may use the query like this...
SELECT clientid, SUM(items) as A FROM mytable

- Anand


That should be
SELECT clientid, SUM(items) as A FROM mytable group by Clientid

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

eurob
Posting Yak Master

USA
100 Posts

Posted - 09/01/2005 :  10:04:18  Show Profile  Visit eurob's Homepage  Send eurob an ICQ Message  Reply with Quote

quote:

SELECT @a = sum(items) from MyTable
where Clientid=someId


This does not work, I get the same error msg as I got with my first post. I guess I would have to work with a temp table ? I need to use variables because within the select I want to add and subtract later on.



robert
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/01/2005 :  10:11:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/01/2005 :  10:57:30  Show Profile  Reply with Quote
What's the problem you are wanting to solve?

When I look at your example:

declare @a int
select clientid,@a = sum(items) from mytable

it looks like you are trying to output "clientid", but "keep" the @a total for some further processing - and at that point I am sure I have misunderstood your problem!!

Kristen
Go to Top of Page

ke.neelima
Starting Member

6 Posts

Posted - 12/11/2008 :  06:19:03  Show Profile  Reply with Quote
Check out the below link..
http://www.sql-server-helper.com/error-messages/msg-141.aspx
Hope its useful..!


Neelima
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 12/11/2008 :  07:51:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by ke.neelima

Check out the below link..
http://www.sql-server-helper.com/error-messages/msg-141.aspx
Hope its useful..!


Neelima


Yes. Already suggested.

How did you find out this old thread?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

shijobaby
Starting Member

India
44 Posts

Posted - 08/21/2009 :  09:31:05  Show Profile  Visit shijobaby's Homepage  Reply with Quote
Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com/2009/08/msg-141-select-statement-that-assigns.html


Happy Programming
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/21/2009 :  09:36:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by shijobaby

Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com/2009/08/msg-141-select-statement-that-assigns.html


Happy Programming


So, you are creating blog entries based on the error messages posted here?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000