SQL Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Error

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-07-07 : 09:53:54

When I use the statement below I get an error

SELECT     BillingTransactionIndex, CreateDate, UserType, UserIndex, TransType, Reference, Total, Balance,
                          (SELECT     Balance AS Expr1
                            FROM          dbo.BillingTransactions2
                            WHERE      (UserIndex = T.UserIndex) AND (BillingTransactionIndex < T.BillingTransactionIndex) AND (TransType = 1)) 
                      AS 'PreviousInvoiceBalance'
FROM         dbo.BillingTransactions2 AS T

Error below

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Not quite sure how to solve this

ssnaik84
Starting Member

15 Posts

Posted - 2008-07-07 : 10:00:02

try with self join.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:01:16

your subquery is returning more than 1 rows as the error message shows.

Can't really help without knowing your table structure, primary key etc

perhaps you are missing a sum() there ?


(SELECT     sum(Balance) AS Expr1
FROM          dbo.BillingTransactions2
WHERE      (UserIndex = T.UserIndex) AND (BillingTransactionIndex < T.BillingTransactionIndex) AND (TransType = 1))

KH
[spoiler]Time is always against us[/spoiler]

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-07 : 10:01:34

You need to ensure that the SELECT statement within the brackets returns only 1 value, as a short term you may use a way to return only the top value. Long terms you have to change the sql statement to return just 1 value

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-07-07 : 10:03:25

quote:
Originally posted by ssnaik84

try with self join.

How do you SELF JOIN

ssnaik84
Starting Member

15 Posts

Posted - 2008-07-07 : 10:08:35

I don't know exact data..but your query will be similar to...

SELECT T1.BillingTransactionIndex, T1.CreateDate, T1.UserType, T1.UserIndex, T1.TransType, T1.Reference, T1.Total, T1.Balance,
T2.Balance AS 'PreviousInvoiceBalance'
FROM
dbo.BillingTransactions2 AS T1
INNER JOIN dbo.BillingTransactions2 AS T2
ON T1.UserIndex = T2.UserIndex
AND (T1.BillingTransactionIndex < T2.BillingTransactionIndex) AND (T1.TransType = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources