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 @NetCommission decimal

Declare @AccountNumber varchar(50)

Set

@NetCommission = (select (CommissionRebate * Quantity)

from

Account A

Join

Trades T on A.AccountNumber = T.AccountNumber)

Set

@AccountNumber = (select A.AccountNumber

from

cmsAccount A

Join

Trades T on A.AccountNumber = T.AccountNumber)

 

Insert

into Transaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@NetCommission

,

@AccountNumber

)


Mystic Nation
0
mysticnation
11/13/2006 10:31:53 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

13 Replies
234 Views

Similar Articles

[PageSpeed] 18

 Hi 

Insert

into Transaction

(

Payee

..........

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


Sincerely,
Young Fang
0
yyy8347
11/14/2006 1:05:02 AM
Or always embed such object names in [] or "" to avoid "syntax error":

Insert
into [Transaction]

(

Payee...


Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/14/2006 10:21:13 AM

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 @NetCommission decimal

Declare @AccountNumber varchar(50)

Set

@NetCommission = (

select

(CommissionRebate * Quantity)

from

cmsAccount A

Join

cmsTrades T on A.AccountNumber = T.AccountNumber)

Set

@AccountNumber = (select A.AccountNumber

from

cmsAccount A

Join

cmsTrades T on A.AccountNumber = T.AccountNumber)

 

Insert

into TradeTransaction

(

Payee

,

Deposit

,

AccountNumber

)

Values

(

'Account Credit'

,

@NetCommission

,

@AccountNumber

)

Go


Mystic Nation
0
mysticnation
11/14/2006 9:06:36 PM
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)

Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/15/2006 3:08:24 AM

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 A on A.AccountNumber = T.AccountNumber

where

T.TradeDate ='11/13/2006'

GROUP

BY T.AccountNumber

 


Mystic Nation
0
mysticnation
11/21/2006 12:28:49 AM

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

@NetCommission decimal

Declare

@AccountNumber Varchar(50)

 

Select

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

from

cmsTrades T

Join

cmsAccount A on 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

)


Mystic Nation
0
mysticnation
11/21/2006 12:41:17 AM
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.
Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/21/2006 9:32:16 AM

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

appCursor Cursor

 

For

Select

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

from

cmsTrades T

Join

cmsCalcs c on 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

@NetCommission decimal, @AccountNumber Varchar(50)

Fetch

Next from appCursor Into @NetCommission, @AccountNumber

While

(@@Fetch_Status<>-1)

Begin

If

(@@Fetch_Status <>-2)

Insert

into appTransaction

(

Payee

,

Payment

,

AccountNumber

)

Values

(

'Client Credit'

,

@NetCommission

,

@AccountNumber

)

Fetch

Next from appCursor Into @NetCommission, @AccountNumber

End

Close

appCursor

DEALLOCATE

appCursor

Go


Mystic Nation
0
mysticnation
11/22/2006 5:53:42 PM
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?


Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/23/2006 2:26:32 AM

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
 

 



Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
11/23/2006 2:50:51 AM
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?
Mystic Nation
0
mysticnation
11/29/2006 12:57:34 AM

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

 


Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
11/29/2006 1:45:54 AM
That did the trick perfectly...thank you!
Mystic Nation
0
mysticnation
11/29/2006 8:01:05 AM
Reply:

Similar Artilces:

SQL Logging SQL Statement
Hi at All!! I use ASA 7 so I'd like to know if there is a way to logging the sql instruction that database server dbsrv7, receive from clients ? I need this because a client that use odbc connection after performing an insert into a table, the client receive the Message: "SQL truncate". I need to know the field on the table that generate this warning!! Thanks in advance Massimo The tool that does this is called "request-level logging" and in V7 you have to enable it from the command line when you start the server, using the -zr (and optionally -zo) c...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Viewing a SPIDs last SQL statement using sqltext()...though sometimes only partial SQL statements are shown
The dbcc sqltext() command seems to allow you to view the last SQL statement issued by an active SPID on the server. However I've noticed that sometimes only part of the SQL statement is returned - does anyone know why this might be, and if so whether it's due to a server configuration. Anyway, here's my notes on how to view the last SQL statement processed: ** How to see the last SQL a SPID has processed -- dbcc traceon(3604) Directs trace output to the client, rather than to the error log 1> dbcc traceon(3604) 2> go DBCC execution completed. If DBCC pr...

RFC: SQL Extensions for SQL::Statement [Long]
I will be releasing a significantly upgraded SQL::Statement and DBD::File shortly and I have some questions about interface. I'd really appreciate some feedback. These are the features that are near finalization: * heterogeneous SQL across multiple DBI sources * per-table DBI connections on a single dbh * improved API for DBD writers * support for in-memory tables ala DBD::RAM for all SQL::Statement DBDs * improved ability to specify SQL dialect features on a per-DBD, per-session, or per-statement basis * user-supplied functions, procedures, and persistent ...

sql statement limit in Execute SQL Task
i have two machines. i was working in a "Execute SQL Task" object's SQL window on a rather long sql task on one machine and reached some kind of limit on the length of the sql statements. i can not add another line of code. i cut and pasted this same code into the exact same "Execute SQL Task" object's SQL window on the second machine and it does not have this limit. does anyone know what causes this? (in fact....i could paste it in twice - doubling the length)   What do you mean by "i cut and pasted this same code into the exact same screen on the se...

Dynamic SQL Format 2 SQL statement
Hi , I tried to implement Dynamic SQL Format 2 SQL statement but it is running fine with out error but it is not saving data to data base INT Dept_id_var =3d 156 String Dept_name_var SetNull(Dept_name_var) PREPARE SQLSA FROM "INSERT INTO dept VALUES (?,?) using my_transaction " ; EXECUTE SQLSA USING :Dept_id_var,:Dept_name_var ; [1] Commit using my_transaction ; [2] To test I tried my_transaction.autocommit =3d true my SQLSA satatement =85. my_transaction.autocommit =3d false but in both case I am not able to insert data to database...

SQL Anywhere 11 hanging on SQL statement
I have a 800mb DB that I unloaded/reloaded from version 8 to the Developer Edition of version 11. If I open ISQL and run: alter dbspace SYSTEM add 100 MB the DB file immediately grows by 100MB, but the execution of the statement never finishes. However if I use Sybase Central to create a DB and then perform the same procedure on this new empty DB, the statement executes in under 30 seconds. If I try pressing the "stop" button in ISQL, this locks up that program. And if I press the "Shutdown" button on the SQL Anywhere console window, the server also...

Executing the i-sql statements using i-sql interface.
I am writing the c/c++ interface which will execute i-sql query statements to retrive information from the isql. It involves parsing the isql output to get the required information objects ( database, devices, ...etc). This parsing string method seems to be not appropriate, as the format of the isql statement might change in future release of ASEs. I am wondering is there any otherway I can access the information(like querying info of databases, devices, ...etc) from sybase server from "C/C++" program in structural manner??. Is it possible to get the return status(succe...

TreeView (Difficulty in SQL within SQL statement)
I have difficulty in getting results of the Query that is within another query in Tree View Structure. The code is as: ourConnection = New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\ReportWritingTool\ekindb") ourConnection.Open() ourSQLQuery = New OdbcCommand("SELECT DISTINCT SISY, SISYR, DRDL01, SIFUNO, SIMD, SIOBNM FROM F0005, F9860 WHERE (F0005.DRKY = TRIM(F9860.SISY)) AND (F9860.SIFUNO='TBLE' OR F9860.SIFUNO='BSVW'AND F0005.DRSY='98' AND F0005.DRRT='SY');", ourConnection) ourDataAdapter = New Odb...

SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help  it's in selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'" if i delete the condition the " ELSE " appear. otherwise it's not appear whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

SQL > My SQL
I have a problem, my asp.net page wants to connect to the sql server, but it has to be the mysql. How can I solve this problem? This is my code: <%@ page explicit="true" language="VB" debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script runat="server"> sub page_load(sender as object, e as eventargs) If Not IsPostBack Then dim connect as sqlconnectio...

SQL Express to SQL ?
I am developing a site using the express edition of all tools (VWD and SQL Express).  I want to use GoDaddy hosting and was wondering if I will be able to effectively put my SQL database and website up on their hosting even though it was developed via Express edition of Microsoft programs?...

Retrieving sql error position in a sql statement (informix)
I've developped an tool with that help developpers on Stored Procedure developing. It's possible to retriev the procedure code and modifying it on a RichTextEdit, with keyword color, ... When the script is send to the database (executing a Create Procdedure statement) and that there's an error in it, the only information I have is the sqlcode, sqldbcode and sqlerrtext. But none of these give me the position of the error like DBACCESS tool do. Can someone help me ? ...

Web resources about - complex sql statement - asp.net.sql-datasource

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Paris attacks: Grand Mufti of Australia Dr Ibrahim Abu Mohammed issues clarifying statement condemning ...
The Grand Mufti&nbsp;of Australia says he has always &quot;consistently and&nbsp;unequivocally condemned all forms of terrorist violence&quot;. ...

"No justification for the taking of innocent lives": Mufti clarifies statement
The Grand Mufti of Australia has hit back at criticisms that his comments following the deadly Paris attacks justified terrorism, saying he &quot;unequivocally ...

Presidential Statement and Press Conference
Came back from walking the dog to find that the adult was in the room and speaking clearly about events. The link to the live feed is here. ...

Liveblogging World War II: Robert H. Jackson: Opening Statement before the International Military Tribunal ...
**Robert H. Jackson**: [Opening Statement before the International Military Tribunal](https://www.roberthjackson.org/speech-and-writing/open ...

Eagles of Death Metal issue statement on Paris terror attack
Previously: Josh Homme's Sweet Stuff Foundation raising money for families of Paris attack victims Nick Alexander was Eagles of Death Metal's ...

ISIS is 'trying to taunt' Anonymous with a new statement on the hackers' declaration of 'war'
A propaganda account affiliated with ISIS has released another statement related to the hacking collective Anonymous' declaration of "war" on ...

PETA Statement Commemorating Hero Police Dog Killed In Paris Raid
PETA Statement Commemorating Hero Police Dog Killed In Paris Raid

Eagles of Death Metal Issue Statement on Paris Attacks: 'Love Overshadows Evil'
The rock band send their condolences to the victims of the terrorist attack on the French capital and their loved ones.

Resources last updated: 11/22/2015 2:59:41 AM