troubleshooting Question

SQL - Subquery Trouble more than one value returned

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
7 Comments1 Solution21 ViewsLast Modified:
Hi EE,

I have the following query:

SELECT VENDTRANS.ACCOUNTNUM, SUM (VENDTRANSOPEN.AMOUNTMST)      AS [ AMOUNT TO PAY ], VENDTRANSOPEN.DUEDATE AS DueDate
         , ( SELECT    
        SUM ( CUSTTRANSOPEN . AMOUNTMST )        
              FROM SALESTABLE
            INNER JOIN CUSTINVOICEJOUR ON CUSTINVOICEJOUR . DATAAREAID    = SALESTABLE . DATAAREAID AND
                                CUSTINVOICEJOUR . ORDERACCOUNT = SALESTABLE . INVOICEACCOUNT AND
                                CUSTINVOICEJOUR . SALESID       = SALESTABLE . SALESID    
                     INNER JOIN CUSTTRANS ON CUSTTRANS . DATAAREAID   = CUSTINVOICEJOUR . DATAAREAID AND
                                       CUSTTRANS . VOUCHER  = CUSTINVOICEJOUR . LEDGERVOUCHER AND
                                       CUSTTRANS . ACCOUNTNUM = CUSTINVOICEJOUR . INVOICEACCOUNT AND
                                       CUSTTRANS . TRANSDATE   = CUSTINVOICEJOUR . INVOICEDATE
      LEFT OUTER JOIN CUSTTRANSOPEN ON CUSTTRANS . DATAAREAID         = CUSTTRANSOPEN . DATAAREAID AND
                                            CUSTTRANS . ACCOUNTNUM         = CUSTTRANSOPEN . ACCOUNTNUM AND
                                            CUSTTRANS . RECID     = CUSTTRANSOPEN . REFRECID AND
 
 CUSTTRANSOPEN.DUEDATE = VENDTRANSOPEN.DUEDATE AND
 SALESTABLE . DATAAREAID = 'EF'
            GROUP BY
         CUSTTRANSOPEN . DUEDATE
 
 ) AS   [ AMOUNT RECEIVABLE ]
       
    FROM PURCHTABLE
        INNER JOIN VENDTABLE ON VENDTABLE.DATAAREAID = PURCHTABLE.DATAAREAID AND VENDTABLE.ACCOUNTNUM = PURCHTABLE.ORDERACCOUNT    
    INNER JOIN VENDINVOICEJOUR ON VENDINVOICEJOUR.DATAAREAID = PURCHTABLE.DATAAREAID AND VENDINVOICEJOUR . ORDERACCOUNT = PURCHTABLE . ORDERACCOUNT AND VENDINVOICEJOUR.PURCHID = PURCHTABLE.PURCHID
             INNER JOIN VENDTRANS ON VENDTRANS.DATAAREAID= VENDINVOICEJOUR.DATAAREAID AND VENDTRANS . VOUCHER = VENDINVOICEJOUR . LEDGERVOUCHER AND VENDTRANS.ACCOUNTNUM = VENDINVOICEJOUR.INVOICEACCOUNT AND VENDTRANS.TRANSDATE = VENDINVOICEJOUR.INVOICEDATE
    INNER JOIN VENDTRANSOPEN ON VENDTRANS.DATAAREAID = VENDTRANSOPEN.DATAAREAID AND VENDTRANS.ACCOUNTNUM = VENDTRANSOPEN.ACCOUNTNUM AND VENDTRANS.RECID = VENDTRANSOPEN.REFRECID
where --VENDTRANSOPEN.DUEDATE >= CONVERT (DATETIME, '2018-01-01', 103)   AND VENDTRANSOPEN . DUEDATE <= CONVERT (DATETIME, '2018-10-12', 103 )
 PURCHTABLE.DATAAREAID = 'EF'
AND VENDTRANSOPEN.DUEDATE <> CONVERT (DATETIME, '01 /01/1900 ' , 103 ) -- AND VENDTABLE.VENDGROUP NOT IN ('CLIENTS')

GROUP BY
VENDTRANS.ACCOUNTNUM, VENDTRANSOPEN.DUEDATE
I am getting the following error:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

How would I modify my query to accommodate this any assistance is appreciated?

Thank you.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros