T-SQL Subquery Results

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
LVL 3
fjkaykr11Asked:
Who is Participating?
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
fjkaykr11Author Commented:
@ 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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
fjkaykr11Author Commented:
@jimhorn I did try that as well to specify
this as  Payment.PaymentDate and I get the same error.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
It would greatly help if you could post your entire query + subquery, as opposed to just the subquery.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
fjkaykr11Author Commented:
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
 
fjkaykr11Author Commented:
@ angelIII sorry that was a typo, it should be Orders
0
 
fjkaykr11Author Commented:
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
 
fjkaykr11Author Commented:
@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
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.

All Courses

From novice to tech pro — start learning today.