Filtering by alias? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Filtering by alias?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-05-22 : 18:30:29

I have an SQL command which is almost finished. Basically it's a stock age report. It compares my current inventory against my purchase orders. For every row, it gives me the stock balance. When the balance becomes a negative value, that's when I know that stock did not exist before that Purchase order was introduced. I see this information in the field labeled 'CalcNum'. That alias is the column which gives me the balance of my stock.

What I wanted to do was to have my subquery get the ID of that row. Then to filer the greater query WHERE the ID is equal to that result. The catch is that I've given it an alias of 'CalcNum' and now I can't filter 'WHERE CalcNum.ID=(subquery)' because it doesn't recognize CalcNum...

Does anyone know how I could make this work? Or of a workaround?

SELECT pir.id,aggregateQty,-qtyreceived, (aggregateQty - qtyreceived), ReceivedOn
,
(
SELECT SUM (PurchaseItemReceive.qtyreceived)
	FROM bvc_product pp
	INNER JOIN PurchaseItemReceive on PurchaseItemReceive.ProductID = pp.id
	WHERE pp.ID = p.id
	AND PurchaseItemReceive.ReceivedOn  < pir.ReceivedOn
	GROUP BY PurchaseItemReceive.qtyreceived
	) as 'preCalc'

,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
	FROM bvc_product pp
	INNER JOIN PurchaseItemReceive on PurchaseItemReceive.ProductID = pp.id
	WHERE pp.ID = p.id
	AND PurchaseItemReceive.ReceivedOn  < pir.ReceivedOn
	GROUP BY PurchaseItemReceive.qtyreceived
	)-AggregateQty as 'CalcNum'

	FROM bvc_product p
	INNER JOIN PurchaseItemReceive pir on pir.ProductID = p.id
	WHERE p.ID = 'APLLEPIE'

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-22 : 21:14:10

Here is a way to get what you want:
[CODE]

SELECT * FROM
(SELECT pir.id,aggregateQty,-qtyreceived, (aggregateQty - qtyreceived), ReceivedOn
,
(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
INNER JOIN PurchaseItemReceive on PurchaseItemReceive.ProductID = pp.id
WHERE pp.ID = p.id
AND PurchaseItemReceive.ReceivedOn < pir.ReceivedOn
GROUP BY PurchaseItemReceive.qtyreceived
) as 'preCalc'

,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
INNER JOIN PurchaseItemReceive on PurchaseItemReceive.ProductID = pp.id
WHERE pp.ID = p.id
AND PurchaseItemReceive.ReceivedOn < pir.ReceivedOn
GROUP BY PurchaseItemReceive.qtyreceived
)-AggregateQty as 'CalcNum'

FROM bvc_product p
INNER JOIN PurchaseItemReceive pir on pir.ProductID = p.id
WHERE p.ID = 'APLLEPIE') A
WHERE CalcNum < 0

[/CODE]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-05-23 : 00:29:11

Thanks! I thought it would be a subtle difference like that. I'm so close, but not there yet.

I am getting this error message:

Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'WHERE'.

Screenshot:

-Sergio
I use Microsoft SQL 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:50:19

you're missing an alias for the derived table
put an alias (say t) before where to make it like


...
)t
WHERE CalcNum < 0

See how previous suggestion had it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SergioM
Posting Yak Master

170 Posts

Posted - 2013-05-23 : 10:52:14

Hmm, unfortunately that didn't work. Adding the 't' or 'A' just gives me more errors. Also, when I attempt the WHERE clause from t., it doesn't show CalcNum as an option.

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-23 : 11:00:33

Try this:
[CODE]

SELECT * FROM
(SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn
,
(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
INNER JOIN PurchaseItemReceive on PurchaseItemReceive.ProductID = pp.id
WHERE pp.ID = p.id
AND PurchaseItemReceive.ReceivedOn < pir.ReceivedOn
GROUP BY PurchaseItemReceive.qtyreceived
) as 'preCalc'

,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
INNER JOIN PurchaseItemReceive on PurchaseItemReceive.ProductID = pp.id
WHERE pp.ID = p.id
AND PurchaseItemReceive.ReceivedOn < pir.ReceivedOn
GROUP BY PurchaseItemReceive.qtyreceived
)-AggregateQty as 'CalcNum'

FROM bvc_product p
INNER JOIN PurchaseItemReceive pir on pir.ProductID = p.id
WHERE p.ID = 'APLLEPIE') A
WHERE CalcNum < 0

[/CODE]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-05-23 : 11:10:59

Pure Genius! Thank you very much for the help! :)

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-23 : 20:33:47

Glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources