select statement syntax - SQL Server Forums

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)

select statement syntax

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-19 : 17:41:23

Please help.

I have a statement below that runs ok. I'm trying to
put the result from this statement into a variable
(testfld) and I'm getting a syntax error (incorrect syntax near 'b').

All I want to do is put the results from the select statement
that runs into variable testfld. How do I do this?

------------------- this runs ok -----------------------------

select avg(amount) from
(select a.amount from acct_file a
cross join acct_file b
group by a.amount
having sum(sign(a.amount - b.amount)) in (1,0,-1)
) a

----------------- this doesn't run ---------------------------

declare @testfld numeric(9)

select @testfld =
(
select avg(amount) from
(select a.amount from acct_file a
cross join acct_file b
group by a.amount
having sum(sign(a.amount - b.amount)) in (1,0,-1)
) a
) b

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-19 : 17:47:33

When assigning a variable dont alias the subquery.

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-19 : 17:53:27

Thanks. I guess was overlooking the obvious.

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-01-20 : 03:30:24

declare @testfld numeric(9)

select @testfld = avg(amount)
from ( select a.amount from acct_file a
cross join acct_file b
group by a.amount
having sum(sign(a.amount - b.amount)) in (1,0,-1)
) a

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources