Error when Modifying a Query

[dwg23]

Hello,
I am attempting to modify the below query so that the rec.DateReceived shows all of the dates received, not just the "Max" date. When I remove the "Max" part I get the error of "SubQuery Returned more than 1 value. This is knot permitted when the subquery follows =, != etc... if I rewrite the query so that it is just whreceipt.datereceipt then I get the error that the multi-part identifier could not be bound.

Any ideas?

select
pol.PONumber,
vpf.Name as 'Vendor Purchase From',
pol.Item,
pol.QuantityReceived,
(select max(rec.DateReceived)
from WHReceipt rec join WHReceiptLine recl on rec.ReceiptNumber = recl.ReceiptNumber
where rec.PONumber = pol.PONumber and recl.POLineNumber = pol.POLineNumber
) as 'date received',
pol.RequiredDate,
pol.OrderDate,
pol.PromiseDate,
pol.ExpectedDate,
(
select forecastLeadTime
from IMItemVendorLoc
where location = 'SEA'
and vendor = vpf.vendor
and item = pol.item
) as 'Forecast Lead Time'
from POPOLine pol join POPO po on pol.PONumber = po.PONumber
join APVendorPurchaseFrom vpf on vpf.VendorPurchaseFrom = po.VendorPurchaseFrom
where pol.Status <> 'CANCEL' and pol.RequiredDate >= '{[Report]!B7}'
and pol.RequiredDate < '{[Report]!B8}' and pol.ShipTo = 'SEA'
order by pol.RequiredDate, vpf.Name

 

[TheBugSlayer]

dwg23, two problems:
1- 'Date Received' is a column with a value coming from a sub-query and as such, ONLY ONE value is expected. If you remove the aggregate function you them get all different rows in the sub-query and this is not permitted. To solve this what you need to do is take out the sub-queries and JOIN the rest of the query to it.

2- The multi-part identifier not found issue is related to 1. If you remove the whole sub-query then the rest of the query does not know what WHReceipt is anymore, whether it is a table alias or a table name.

What you are looking for should look approximately like this:

select
pol.PONumber,
vpf.Name as 'Vendor Purchase From',
pol.Item,
pol.QuantityReceived,
[bold]
rec.DateReceived AS 'Date Received',
[/bold]
pol.RequiredDate,
pol.OrderDate,
pol.PromiseDate,
pol.ExpectedDate,
(
select forecastLeadTime
from IMItemVendorLoc
where location = 'SEA'
and vendor = vpf.vendor
and item = pol.item
) as 'Forecast Lead Time'
from POPOLine pol 
join POPO po on pol.PONumber = po.PONumber
join APVendorPurchaseFrom vpf on vpf.VendorPurchaseFrom = po.VendorPurchaseFrom
[bold]
JOIN WHReceipt rec ON rec.PONumber = pol.PONumber
JOIN WHReceiptLine recl on rec.ReceiptNumber = recl.ReceiptNumber
[/bold]
where pol.Status <> 'CANCEL' 
      and pol.RequiredDate >= '{[Report]!B7}'
      and pol.RequiredDate < '{[Report]!B8}' 
      and pol.ShipTo = 'SEA'
order by pol.RequiredDate, vpf.Name

Play around with it.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[dwg23]

BugSlayer,
Thanks, I will play and see what I come up with.
I had figured out the rec.DateReceived AS 'Date Received' but did not think about the join changes you made.

 

[dwg23]

TheBugSlayer,
Had to do a little tweaking but got it to work.
Thanks again!

dwg23