?
Solved

T-SQL Subquery Results

Posted on 2012-09-04
10
Medium Priority
?
588 Views
Last Modified: 2012-09-04
I need to return information from a column that is specified in a subquery.
In the subquery below,  I am trying to return info from the PaymentDate column.
When I try to Select Order.PaymentDate in my query I get an error Invalid object name for the ORder.PaymentDate.  I tried to specify this ias Payment.PaymentDate but I get the aame error.
Any idea on how I can fix this?

(SELECT ID, SUM(amount) AS purchase FROM Order
WHERE Order.PaymentDate BETWEEN '2012-09-01' AND '2012-09-04'
AND Order.amount >=0
       GROUP BY ID)Payment
0
Comment
Question by:fjkaykr11
10 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 38363676
You don't need to specify the table in the the sub query.

you can do:

(SELECT ID, SUM(amount) AS purchase FROM Order
WHERE PaymentDate BETWEEN '2012-09-01' AND '2012-09-04'
AND amount >=0
       GROUP BY ID) Payment


Does the order table have a paymentDate column?
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38363693
>When I try to Select Order.PaymentDate in my query I get an error Invalid object name for the ORder.PaymentDate.
Your subquery is named Payment, not Order, so if you use Order.PaymentDate in your main query it will be looking for an Order table in the main query, not the subquery.

If you want to grab PaymentDate from your subquery then refer to it as Payment.PaymentDate ...

SELECT blah, blah, blah, Payment.PaymentDate
FROM
   (SELECT ID, PaymentDate, SUM(amount) AS purchase FROM Order
   WHERE Order.PaymentDate BETWEEN '2012-09-01' AND '2012-09-04'
   AND Order.amount >=0
   GROUP BY ID, PaymentDate) Payment

This also means you're grouping by PaymentDate as well.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38363842
@ ged325 yes the order table has a paymentDate column.  I am not seeing a difference in the code the you posted, can you clarify?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 3

Author Comment

by:fjkaykr11
ID: 38363853
@jimhorn I did try that as well to specify
this as  Payment.PaymentDate and I get the same error.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38363869
It would greatly help if you could post your entire query + subquery, as opposed to just the subquery.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38363907
ORDER is a reserved keyword.
use [Order] in your sql:

(SELECT ID, SUM(amount) AS purchase FROM [Order]
WHERE [Order].PaymentDate BETWEEN '2012-09-01' AND '2012-09-04'
AND [Order].amount >=0
       GROUP BY ID)Payment 

Open in new window

0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38363917
Ok.  Keep in mind the query is VERY long and I am sure there are many ways the query could be rewritten (I didn't create this). So I am only looking to return the results, not rewrite any other section.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38363968
@ angelIII sorry that was a typo, it should be Orders
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38363994
I don't think I can post the entire query, there are somethings I need to change it is going to be too difficult.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 38364232
@jimhorn I was able to get some help using another site.  I was missing the table.column reference within the subquery, I only had it specified as a WHERE Clause

This works:
SELECT Orders.PaymentDate as [Payment Date]

(SELECT ID, Orders.PaymentDate AS PaymentDate,
WHERE Orders.PaymentDate BETWEEN '2012-09-01' AND '2012-09-04'
AND Orders.Amount >=0
GROUP BY mbrs_ky, Orders.PaymentDate)Payment
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question