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
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.