CODEWITHSUNDEEP
sql-servert-sql
s.k.Soni
s.k.Soni

Reputation: 1310

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

I am getting that title error when I am doing select rows in the view of sql server.

here is my code

CREATE VIEW vw_cashbook AS
SELECT cashbooktranno, 
       cashbookdate, 
       cashbookrefaccname, 
       cashbookgroupname, 
       cashbookaccname, 
       cashbookdescription, 
       cashbookreceipt, 
       cashbookpayment,
       (select sum(cashbook.cashbookreceipt) - sum(cashbook.cashbookpayment) from tbl_cashbook) as totalpayment,
       company.companyname
FROM tbl_cashbook cashbook 
  LEFT OUTER JOIN tbl_companyregistration company 
               ON cashbook.branchid = company.companyid
group by cashbooktranno, cashbookdate, cashbookrefaccname, cashbookgroupname, cashbookaccname, 
cashbookdescription, cashbookreceipt, cashbookpayment, company.companyname

Now, this code is for to create the view from the table. This code is very perfect and hence as per the code my view also created. Now, the problem is that when I am select the rows from the created view or edit the view at that time it shows the error

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

Here is my select query for the view

SELECT TOP 1000 [cashbooktranno]
      ,[cashbookdate]
      ,[cashbookrefaccname]
      ,[cashbookgroupname]
      ,[cashbookaccname]
      ,[cashbookdescription]
      ,[cashbookreceipt]
      ,[cashbookpayment]
      ,[totalpayment]
      ,[companyname]
  FROM [AutoFinanceSoftware].[dbo].[vw_cashbook]

So is there any problem in my query or is there any other problem.

Upvotes: 0

Views: 1301

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

There is no need for subquery. Use:

CREATE VIEW vw_cashbook AS
SELECT cashbooktranno, 
       cashbookdate, 
       cashbookrefaccname, 
       cashbookgroupname, 
       cashbookaccname, 
       cashbookdescription, 
       cashbookreceipt, 
       cashbookpayment,
       sum(cashbook.cashbookreceipt) - sum(cashbook.cashbookpayment) as totalpayment,
       company.companyname
FROM tbl_cashbook cashbook 
  LEFT OUTER JOIN tbl_companyregistration company 
               ON cashbook.branchid = company.companyid
group by cashbooktranno, cashbookdate, cashbookrefaccname, cashbookgroupname, cashbookaccname, 
cashbookdescription, cashbookreceipt, cashbookpayment, company.companyname;

Or even (if both columns are NOT NULL):

CREATE VIEW vw_cashbook AS
SELECT cashbooktranno, 
       cashbookdate, 
       cashbookrefaccname, 
       cashbookgroupname, 
       cashbookaccname, 
       cashbookdescription, 
       cashbookreceipt, 
       cashbookpayment,
       sum(cashbook.cashbookreceipt - cashbook.cashbookpayment) as totalpayment,
       company.companyname
FROM tbl_cashbook cashbook 
  LEFT OUTER JOIN tbl_companyregistration company 
               ON cashbook.branchid = company.companyid
group by cashbooktranno, cashbookdate, cashbookrefaccname, cashbookgroupname, cashbookaccname, 
cashbookdescription, cashbookreceipt, cashbookpayment, company.companyname

Upvotes: 2

Related Questions