Baq

Hi
I would be grateful if some one can help me convert this sql query into a BAQ or is there a better way please.

SELECT p.ShortChar01 AS SkuType, ISNULL(w.OnHandQty, 0) - ISNULL(w.DemandQty, 0) + ISNULL(x.BalQty, 0) AS AvailQty  
FROM Part AS p WITH (NOLOCK) 
INNER JOIN PartWhse AS w WITH (NOLOCK) ON p.Company = w.Company AND p.PartNum = w.PartNum 
LEFT OUTER JOIN 
	(SELECT d.PartNum, r.WarehouseCode, SUM(r.RelQty - r.ReceivedQty) AS BalQty 
		FROM PODetail d WITH (NOLOCK) 
		INNER JOIN PORel r WITH (NOLOCK) ON d.Company = r.Company AND d.PONUM = r.PONum AND d.POLine = r.POLine 
		WHERE r.RelQty - r.ReceivedQty > 0 AND d.VoidLine = 0 AND d.OpenLine = 1 AND r.VoidRelease = 0 AND r.OpenRelease = 1 
		GROUP BY d.PartNum, r.WarehouseCode) x ON p.PartNum = x.PartNum AND w.WarehouseCode = x.WarehouseCode 
		WHERE p.PartNum = @p AND w.WarehouseCode = @w

Thank you

I’m not sure anyone is going to do it for you, but if you have specific questions while attempting to do it yourself, folks would likely be more happy to answer those.

Ok thanks.

Hi
Do you know how to include where clauses in a BAQ please?
I am trying to add:-

WHERE r.RelQty - r.ReceivedQty > 0 AND d.VoidLine = 0 AND d.OpenLine = 1 AND r.VoidRelease = 0 AND r.OpenRelease = 1

Thanks

This one would be in the SubQuery Criteria section.

These would be in the Table Criteria section for each of the relevant tables.

That’s great, many thanks for your help.

1 Like

Hi
I am trying to add ‘r.RelQty - r.ReceivedQty > 0’ in the subquery criteria but there does not seem a way to do this. Have you got any ideas please?

Thanks

Make a calculated field with that formula then add the > 0 in the subwuery tab

That’s great many thanks.