Subquery returned more than 1 value.

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)

Subquery returned more than 1 value.

Author

Topic

randheer
Starting Member

15 Posts

Posted - 2010-12-14 : 09:05:04

Hi,
My Query is:


SELECT F.FFCBillDate AS 'Bill Date',F.FFCBillNo AS 'Bill Number',    
(SELECT vendorName from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Vendor Name', 
(SELECT PaymentProcessId from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Processing ID',
(SELECT insuranceCertificateExperation from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'COI Expiration',   
(SELECT paymentTerms from tblPaymentTerms where paymentTermsId IN (SELECT paymentTermsId from tblNonPreferredVendor
 where vendorId=F.VendorID)) AS 'Terms',
FDt.CandidateName AS 'CandidateName',FDt.CustomerJob AS 'Customer Job',FDt.Quantity AS 'Hrs/Qty',FDt.Rate,
FDt.Amount AS 'Bill Amount',F.BillPaidDate AS 'Payment Date',
(select job_id from ts_joborder where cand_name=FDt.CandidateName and qbook_item=FDt.qtimebookitem) AS 'JOB CODE'
from tblVendorFFCBill AS F    
INNER JOIN tblVendorFFCBillDetails AS FDt     
ON F.FFCBillID=FDt.FFCBillID 
WHERE F.FFCBillDate BETWEEN '2010-01-01' AND '2010-12-14' AND IsBillPaid IS NULL

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.

A subquery in it:
(select job_id from ts_joborder where cand_name=FDt.CandidateName and qbook_item=FDt.qtimebookitem) AS 'JOB CODE'
returns more than one row which is creating problems. Also I have to show all returned rows from subquery too.
Please tell me. how to solve it.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-14 : 09:14:44

Try:
SELECT F.FFCBillDate AS 'Bill Date',F.FFCBillNo AS 'Bill Number',
(SELECT vendorName from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Vendor Name',
(SELECT PaymentProcessId from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Processing ID',
(SELECT insuranceCertificateExperation from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'COI Expiration',
(SELECT paymentTerms from tblPaymentTerms where paymentTermsId IN (SELECT paymentTermsId from tblNonPreferredVendor
where vendorId=F.VendorID)) AS 'Terms',
FDt.CandidateName AS 'CandidateName',FDt.CustomerJob AS 'Customer Job',FDt.Quantity AS 'Hrs/Qty',FDt.Rate,
FDt.Amount AS 'Bill Amount',F.BillPaidDate AS 'Payment Date',
tj.job_id AS 'JOB CODE'
from tblVendorFFCBill AS F
INNER JOIN tblVendorFFCBillDetails AS FDt
ON F.FFCBillID=FDt.FFCBillID
INNER JOIN ts_joborder tj on tj.cand_name=FDt.CandidateName and tj.qbook_item=FDt.qtimebookitem
WHERE F.FFCBillDate BETWEEN '2010-01-01' AND '2010-12-14' AND IsBillPaid IS NULL

randheer
Starting Member

15 Posts

Posted - 2010-12-15 : 02:38:23

Thanks for reply but it doesnt solve the problem. It only shoes when there is one related jobcode.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-15 : 02:44:36

Try:

SELECT F.FFCBillDate AS 'Bill Date',F.FFCBillNo AS 'Bill Number',
(SELECT vendorName from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Vendor Name',
(SELECT PaymentProcessId from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Processing ID',
(SELECT insuranceCertificateExperation from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'COI Expiration',
(SELECT paymentTerms from tblPaymentTerms where paymentTermsId IN (SELECT paymentTermsId from tblNonPreferredVendor
where vendorId=F.VendorID)) AS 'Terms',
FDt.CandidateName AS 'CandidateName',FDt.CustomerJob AS 'Customer Job',FDt.Quantity AS 'Hrs/Qty',FDt.Rate,
FDt.Amount AS 'Bill Amount',F.BillPaidDate AS 'Payment Date',
tj.job_id AS 'JOB CODE'
from tblVendorFFCBill AS F
INNER JOIN tblVendorFFCBillDetails AS FDt
ON F.FFCBillID=FDt.FFCBillID
LEFT OUTER JOIN ts_joborder tj on tj.cand_name=FDt.CandidateName and tj.qbook_item=FDt.qtimebookitem
WHERE F.FFCBillDate BETWEEN '2010-01-01' AND '2010-12-14' AND IsBillPaid IS NULL

kashyap.2000
Starting Member

22 Posts

Posted - 2010-12-15 : 04:37:18

SELECT F.FFCBillDate AS 'Bill Date',F.FFCBillNo AS 'Bill Number',
(SELECT top 1 vendorName from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Vendor Name',
(SELECT top 1 PaymentProcessId from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'Processing ID',
(SELECT top 1 insuranceCertificateExperation from tblNonPreferredVendor WHERE vendorId=F.VendorID) AS 'COI Expiration',
(SELECT top 1 paymentTerms from tblPaymentTerms where paymentTermsId IN (SELECT paymentTermsId from tblNonPreferredVendor
where vendorId=F.VendorID)) AS 'Terms',
FDt.CandidateName AS 'CandidateName',FDt.CustomerJob AS 'Customer Job',FDt.Quantity AS 'Hrs/Qty',FDt.Rate,
FDt.Amount AS 'Bill Amount',F.BillPaidDate AS 'Payment Date',
(select top 1 job_id from ts_joborder where cand_name=FDt.CandidateName and qbook_item=FDt.qtimebookitem) AS 'JOB CODE'
from tblVendorFFCBill AS F
INNER JOIN tblVendorFFCBillDetails AS FDt
ON F.FFCBillID=FDt.FFCBillID
WHERE F.FFCBillDate BETWEEN '2010-01-01' AND '2010-12-14' AND IsBillPaid IS NULL

Use this query only to test so dont replace original query with the above query as this is just to find the error . if this query runs correctly then the sub query has some data inconsistency . so you need to change your query in a wiser way .

Failures will either break you or will make you a better perosn....

randheer
Starting Member

15 Posts

Posted - 2010-12-15 : 04:46:59

Thanks bro. the problem is that its giving the result which comes in the "date range" and "IsBillPaid is null". but i want the result also to be vendor specific. How can i pass more than one vendorid's in it and get the result only for them only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources