SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Filtering by alias?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

168 Posts

Posted - 05/22/2013 :  18:30:29  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/22/2013 :  21:14:10  Show Profile  Reply with Quote
Here is a way to get what you want:


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 

Go to Top of Page

SergioM
Posting Yak Master

168 Posts

Posted - 05/23/2013 :  00:29:11  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/23/2013 :  00:50:19  Show Profile  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

168 Posts

Posted - 05/23/2013 :  10:52:14  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/23/2013 :  11:00:33  Show Profile  Reply with Quote
Try this:


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 


Go to Top of Page

SergioM
Posting Yak Master

168 Posts

Posted - 05/23/2013 :  11:10:59  Show Profile  Reply with Quote
Pure Genius! Thank you very much for the help! :)

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/23/2013 :  20:33:47  Show Profile  Reply with Quote
Glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000