complex sql statement

I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(select(CommissionRebate* Quantity)

from

Account A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

Trades Ton A.AccountNumber= T.AccountNumber)

Insert

into Transaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@.NetCommission

,

@.AccountNumber

)

Hi

Insert

intoTransaction

(

Payee

........

Transaction is the key word,you have to choose another table name.

|||Or always embed such object names in [] or "" to avoid "syntax error":

Insert
into [Transaction]

(

Payee...|||

Even with the table name changed I am still getting a subquery error

Subquery returned more than 1 value.

All I really want to do is run a calculation on ech row in the database...any ideas?

Declare @.NetCommissiondecimal

Declare @.AccountNumbervarchar(50)

Set

@.NetCommission=(

select

(CommissionRebate* Quantity)

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Set

@.AccountNumber=(select A.AccountNumber

from

cmsAccount A

Join

cmsTrades Ton A.AccountNumber= T.AccountNumber)

Insert

into TradeTransaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@.NetCommission

,

@.AccountNumber

)

Go

|||Obviously the error indicates?that?some?subquery?returns?more?than?1?value?while?you're?trying?to?assign?the?returned?values?to?a?single?variable.?This?is?a?bad?logic?as?there?is?no?array?in?T-SQL.?So?my?suggestion?would?be?to?use?some?aggregation?funciton?or?TOP?keyword?to?restrict?the?subquery?to?return?only?1?value, for example:

Set @.NetCommission =
(select top 1 (CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)

Or:

Set @.NetCommission =
(select max(CommissionRebate * Quantity)
from cmsAccount A Join
cmsTrades T on A.AccountNumber = T.AccountNumber)|||

This gets me the two values I need, but I then need to insert these two values into a different table... how would I go about doing this?

Select

sum(A.CommissionRebate* T.Quantity)As NetCommission, T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

|||

Ok this one works but it only inserts the first row and runs successfully, but does not go on to insert the other rows...

Declare

@.NetCommissiondecimal

Declare

@.AccountNumberVarchar(50)

Select

@.NetCommission=Sum(A.CommissionRebate* T.Quantity), @.AccountNumber= T.AccountNumber

from

cmsTrades T

Join

cmsAccount Aon A.AccountNumber= T.AccountNumber

where

T.TradeDate='11/13/2006'

GROUP

BY T.AccountNumber

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@.NetCommission

,

@.AccountNumber

)

|||If you want to insert multiple rows you need to use Cursor to skip through the rows to be inserted, or store the rows in a temple table so that you can?fetch rows from it?into?some?table?in?a?single?insert?statement.|||

I did this with the cursor idea, worked for a second and now I am getting this message: Error converting data type varchar to decimal.

DECLARE

appCursorCursor

For

Select

T.AccountNumber,sum(CommissionRebate)/count(*)*sum(Quantity)as NetCommission

from

cmsTrades T

Join

cmsCalcs con c.AccountNumber= T.AccountNumber

where

T.TradeDate=CAST(YEAR(getdate()) as varchar) + RIGHT('00'+CAST(MONTH(getdate()) as varchar), 2) + RIGHT('00'+CAST(DAY(getdate())-1 as varchar), 2)

and

c

.Month= 11

GROUP

BY T.AccountNumber

Open

appCursor

Declare

@.NetCommissiondecimal, @.AccountNumberVarchar(50)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

While

(@.@.Fetch_Status<>-1)

Begin

If

(@.@.Fetch_Status<>-2)

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@.NetCommission

,

@.AccountNumber

)

Fetch

Nextfrom appCursorInto @.NetCommission, @.AccountNumber

End

Close

appCursor

DEALLOCATE

appCursor

Go

|||I found this:

DECLARE
appCursor Cursor For
Select
T.AccountNumber, sum(CommissionRebate) / count(*) * sum(Quantity) as NetCommission
from
cmsTrades T
.....
Fetch Next from appCursor Into @.NetCommission, @.AccountNumber

Shouldn't this be?Fetch Next from appCursor Into @.AccountNumber, @.NetCommission? The same order as the returned fields in the select query?|||

Forget the cursor. You want to stay away from those as much as possible. Keep with set-based logic; it's the SQL way! :-)

Use this sort of approach instead:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
CommissionRebate * Quantity,
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber

|||that works great, would there be an easy way to sum up the inserts by AccountNumber so instead of 36 inserts for one accountnumber I would have just 1 for each accountnumber for that day?|||

Yes, you might try this:

INSERT INTO TradeTransaction
(
Payee,
Deposit,
AccountNumber
)
SELECT
'Account Credit',
SUM(CommissionRebate * Quantity),
A.AccountNumber
FROM
cmsAccount A
INNER JOIN
cmsTrades T on A.AccountNumber = T.AccountNumber
GROUP BY
A.AccountNumber

|||That did the trick perfectly...thank you!