what error means? - 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 2000 Forums

SQL Server Development (2000)

what error means?

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-08 : 19:38:48

I am executing this SP and gets this error on some values of @icn:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Why would other @icn values execute fine? How can I fix this?

Here's my SP:
CREATE PROCEDURE ExcelTotalNotReviewedTransactions
@icn varchar(4)
AS

SET NOCOUNT ON

DECLARE @cycledate datetime

SELECT @cycledate = MAX(dteCycleDate) FROM CycleDateInfo
WHERE txtInternetNum = @icn

SELECT distinct a.txtname, a.txtaccountnum, substring(a.txtaccountingcode,1,5) as txtaccountingcode,

-- select total transactions in the latest cycle date per cardholder
(
SELECT COUNT(*)
FROM dbo.Transactions
GROUP BY txtInternetNum, dteCycleDate, txtAccountNum
HAVING (txtInternetNum = @icn) AND
(dteCycleDate = @cycledate) AND (txtAccountNum = a.txtaccountnum)
) as Total,

--select total "NO" transactions in the latest cycle date per cardholder
(
SELECT COUNT(blnSent)
FROM dbo.Transactions
WHERE (blnSent = 0)
GROUP BY txtInternetNum, dteCycleDate, txtaccountnum, txttransaccode
HAVING (txtInternetNum = @icn) AND
(dteCycleDate = @cycledate) AND (txtaccountnum = a.txtaccountnum) AND (txttransaccode not in (31,52) )
) as TotalNO

FROM account a INNER join transactions t
ON a.txtaccountnum = t.txtaccountnum
WHERE a.txtinternetcompnum = @icn
ORDER BY a.txtName
SET NOCOUNT OFF
GO

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-10-08 : 21:48:44

The message is saying that it can only evaluate against a scalar value. Multiple values obviously throw the error you are getting. Without the line number the error is producing I'm guessing this is the offending portion of code...

HAVING (txtInternetNum = @icn) AND
(dteCycleDate = @cycledate) AND (txtaccountnum = a.txtaccountnum) AND (txttransaccode not in (31,52) )

If you have more than three transaccodes in your table then (txttransaccode not in (31,52) then your group by will return two or more results and cause the error.

If that is not the case then which is the offending line according to Query Analyzer?

hth,
Justin

Have you hugged your SQL Server today?

Edited by - justinbigelow on 10/08/2002 21:49:14

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-09 : 11:01:28

You're right, that's the offending line(s). So how can I fix my SP so that it returns me the count(blnSent) not including the transactions with txttransaccode of 31 and 52.

PeterG
Posting Yak Master

156 Posts

Posted - 2002-10-09 : 12:21:54

Never mind. I rewote the subqueries without the use of having by clause and they worked fine. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources