Link to home
Start Free TrialLog in
Avatar of Mark
Mark

asked on

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I've been programming SLQ Server queries for years and I believe this is the first time I've seen this error. I have the query show below, two select statements. The first select runs just fine and returns values. When the 2nd select executes I get the error:

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

I don't get this message at all. The first select also does a data retrieval operation so just the fact that a data retrieval operation is being performed must not be the whole problem. If I remove the ',@SURpensionTotal = ' bit, it runs OK (but that doesn't do me much good).

What am I doing wrong? Please help!

declare @memberId varchar(11),
            @retireeId varchar(11),
            @retireePension money,
           @SURpensionTotal money

set @memberId = '048011'

select @retireeId = m.retireeId, @retireePension = r.retireePension
from members m
join (select e.memberId, sum(e.amount) retireePension
  from memberEarnings e
  join earningCodes c on c.earningCode = e.earningCode and e.earningCode in ('DRP','PEN')
  where e.enabled = 1 and e.oneShot = 0
  group by e.memberId) r on r.memberId = m.retireeId
where m.memberId = @memberId

select m.retireeId,@SURpensionTotal = sum(e.amount)
from members m
join memberEarnings e on e.memberId = m.memberId and e.enabled = 1 and e.oneShot = 0
  and e.earningCode in ('DRP','PEN')
where (m.terminationDate is null 
  or (dbo.firstOfThisMonth(default) <= m.terminationDate and dbo.firstOfNextMonth(default) > m.terminationDate))
  and m.retireeId = @retireeId
group by m.retireeId

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark
Mark

ASKER

duh! Of course! That makes total sense. I've probably not seen this error before because I haven't made this mistake before. ScottPletcher's solution is what I did in my 1st query. What I need for this query is appari's solution. I don't need to return the retireeId again. I guess I just stuck it in the SELECT out of habit of putting GROUP BY columns in the select list (but that's not needed). Thanks!