[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

SQL From Clause

Hello,
can you please help,
I'm using below statement, I get an error

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

SELECT FinalizedOrders.OrderNo AS [Order Number], Clients.AccountNumber ,
FinalizedOrdersInvoices.InvoiceNumber AS [InvoiceNumber],
(Select TransactionDate from AROpenTransactions where AROpenTransactions.TransactionNumber = FinalizedOrdersInvoices.InvoiceNumber) AS [InvoiceDate]
FROM FinalizedOrders
INNER JOIN Clients ON FinalizedOrders.AccountNumber = Clients.AccountNumber
INNER JOIN Companies ON Clients.CompanyID = Companies.CompanyID
LEFT JOIN ServiceTypes ON FinalizedOrders.ServiceTypeID = ServiceTypes.ServiceTypeID
LEFT JOIN VehicleTypes ON FinalizedOrders.VehicleTypeID = VehicleTypes.VehicleTypeID
INNER JOIN FinalizedOrdersInvoices ON FinalizedOrdersInvoices.OrderNo = FinalizedOrders.OrderNo
Where Clients.AccountNumber = '511'
and
(Select TransactionDate from AROpenTransactions where AROpenTransactions.TransactionNumber = FinalizedOrdersInvoices.InvoiceNumber) >= '2014-01-01' and (Select TransactionDate from AROpenTransactions where AROpenTransactions.TransactionNumber = FinalizedOrdersInvoices.InvoiceNumber) < = '2014-08-15'


Any help is appreciated
0
W.E.B
Asked:
W.E.B
1 Solution
 
Russell FoxDatabase DeveloperCommented:
You're getting more than one InvoiceDate. If that's bad, you'll need to figure out where your data is wrong, but if that's okay, you just need to figure out which one you want.  This takes the most recent one:
(Select TOP 1 TransactionDate from AROpenTransactions where AROpenTransactions.TransactionNumber = FinalizedOrdersInvoices.InvoiceNumber ORDER BY TransactionDate DESC) AS [InvoiceDate]

Open in new window

0
 
W.E.BAuthor Commented:
Thank you,
Much Appreciated.
0
 
Shaun KlineLead Software EngineerCommented:
For performance purposes, you should consider moving the subquery into the FROM clause, as you are performing it three different times, once in the SELECT clause and twice in the WHERE clause.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now