Count syntax error

[mossbs]

Hi guys,

Got the following bit of code throwing a syntax error but no idea why....

declare @total int

set  @total =  select count(distinct member_ref)
				from membership_history
				where member_ref in (select member_ref from member where member_status = 33)
				and datename(m,valid_from) = datename(m,dateadd(m,-1,getdate()))

error is

''Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.''


any ideas whats up with it? the count 'statement' runs fine on its own - just wont let me set the variable to be taht.

Having same problems when trying to update a table row to be the same.

Cheers,

Dan.

 

[gmmastros]

Missing parenthesis:

declare @total int

set  @total =  [!]([/!]select count(distinct member_ref)
                from membership_history
                where member_ref in (select member_ref from member where member_status = 33)
                and datename(m,valid_from) = datename(m,dateadd(m,-1,getdate()))[!])[/!]

Coded this way... you better make sure that there will only EVER be one row returned from the query or you will get another (different) error.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

select @total = count(distinct member_ref)
from ......

 

[mossbs]

cant beleive it was only () to sort it out!! D'OH!!!

cheers buddy.

Dan