sql help

Question

Post reply

sql help

shubhada

Valued Member

Points: 65
More actions
December 9, 2005 at 3:42 am

#92427

I have written one procedure .in that i have put following login.but I got the error.

Declare
@purgecount int,
@totcovchrg money,
@totpaymntamt money,
@totreimamt money

Select
@purgecount = count(D.EventID),
@totcovchrg = sum(CoverChrgAmt),
@totpaymntamt = sum(PayDetail.PaymntAmt),
@totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID

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

Please tell me how to over come this

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

At a first glance, your SQL looks OK to me. Can you provide a script that

1. Creates the tables involved

2. Populates the tables with a few sample data

3. Executes the select and gets the error

Maybe step no. 2 is not necessary to get the error...

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

Here's an example from Northwind that gives the same error:

declare @id int

select @id=categoryid, description from categories where categoryid = 1

The error message is actually quite helpful - you cannot mix variable assignment and non variable assignment in a single SELECT.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 3

Where does he do that (mix variable assignment and non variable assignment)and how should the query be fixed?

shubhada Valued Member Points: 65 More actions · Answer 4

i think here problem is with the assigning the value to variable

select

@purgecount = count(D.EventID),

@totcovchrg = sum(CoverChrgAmt),

@totpaymntamt = sum(PaymntAmt),

@totreimamt = sum(CalcTotalReimAmt)

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

So if you write "select *" instead of the above, then it works? (still looks like varable assignment to me).

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 6

Or better. What happens if you try

Select

count(D.EventID),

sum(CoverChrgAmt),

sum(PayDetail.PaymntAmt),

sum(CalcTotalReimAmt)

from DelReimb D, Reimburse R, ReimburseOut RO,

(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID

from PaymentsDetail

where isnull(InvalidPayInd,'') <> 'Y' and

PaymntInd not in ('A','B','X')

group by ClaimID,EventID) PayDetail

where

D.ClaimID = R.ClaimID and

D.EventID = R.EventID and

D.ClaimID = RO.ClaimID and

D.EventID = RO.EventID and

D.ClaimID = PayDetail.ClaimID and

D.EventID = PayDetail.EventID

Vladan SSC-Insane Points: 22072 More actions · Answer 7

I tried to guess structure of the tables, unfortunately it is impossible to know from which table the columns CoverChrgAmt and CalcTotalReimAmt are. I placed them into the table Reimburse and ran the SQL over empty tables. No problem, no errors. So, if you need more help, it is vital that you post structure of all tables (at least of the columns involved in this SQL) and some sample data in form of DDL (create table ...) and insert (insert into ... values...) statements.

Please check that the SQL you posted is identical with what you are running. It could be a simple typo. I was able to cause this error by deleting one of the @ signs in the select (in the part where you assign values), e.g.

Select

@purgecount = count(D.EventID),

@totcovchrg = sum(CoverChrgAmt),

@totpaymntamt = sum(PayDetail.PaymntAmt),

totreimamt = sum(CalcTotalReimAmt)

from ........

Phil Parkin SSC Guru Points: 246986 More actions · Answer 8

My mistake - I read the post too quickly

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Papillon Hall of Fame Points: 3303 More actions · Answer 9

.....

declare @id varchar(400)

set @id = (select categoryid from categories where categoryid = 1)

select (@id) as id ,description from categories where categoryid = 1

Regards,

Papillon

Carl Federl One Orange Chip Points: 25384 More actions · Answer 10

Please post the SQL statement that you are actually running as the SQL you posted runs with no errors.

Here is guess at the tables

Create table DelReimb

(ClaimIDint not null

,EventIdint not null

)

Create table Reimburse

(ClaimIDint not null

,EventIdint not null

)

Create table ReimburseOut

(ClaimIDint not null

,EventIdint not null

, CoverChrgAmt money not null

, CalcTotalReimAmt money not null

)

Create table PaymentsDetail

(ClaimIDint not null

,EventIdint not null

, PaymntAmt money not null

, InvalidPayInd char(1) not null

,PaymntInd char(1) not null

)

SQL = Scarcely Qualifies as a Language