use field in sql join where clause

[Price Jones]

I am trying to write a crystal report using a sql statement because it runs much much faster. But I am having trouble with some of the linkings. I need to use the result of a link for criteria in subsequent links.

Ok, here is a sample of what my statement looks like:

(The lines marked with ** are the lines in question)

SELECT
Part.PartNum,
Cust.CustNum,
Cust.CustID,
YTD.Qty
FROM
(
SELECT
Pub.Part.PartNum,
Pub.Part.UserChar1 AS CustID
FROM
Pub.Part
) AS Part
LEFT OUTER JOIN (
SELECT
Pub.Customer.CustID,
Pub.Customer.CustNum,
Pub.Customer.Name
FROM
Pub.Customer
WHERE
Pub.Customer.CustID = '1038'
) AS Cust
ON Part.CustID = Cust.CustID
LEFT OUTER JOIN (
SELECT
Pub.OrderDtl.PartNum,
Sum(Pub.OrderDtl.OrderQty) AS Qty
FROM
Pub.OrderHed JOIN Pub.OrderDtl ON
Pub.OrderHed.OrderNum = Pub.OrderDtl.OrderNum
WHERE
**Pub.OrderHed.CustNum = Cust.CustNum AND**
**Pub.OrderDtl.PartNum = Part.PartNum AND**
YEAR(Pub.OrderHed.OrderDate)=YEAR(CURDATE())
GROUP BY
Pub.OrderDtl.PartNum
) AS YTD ON Part.PartNum = YTD.PartNum


Now, I get an error that says:

Part.PartNum cannot be found or is not specified for the query.


I get the same error for Cust.CustNum. Will you help me figure out what I am doing wrong? Thanks!

Continue reading...