Post reply

Subquery returned more than 1 value.

  • December 14, 2010 at 7:07 am

    #97613

    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.

  • December 14, 2010 at 7:11 am

    #1262184

    You'll need to move the subquery into the From clause as a Cross Apply or a derived table that you can join to. That will mean the data from the rest of the query will have multiple rows, 1 per row in the subquery. Is that what you need?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 20, 2010 at 3:02 am

    This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply