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
 General SQL Server Forums
 New to SQL Server Programming
 How to use Column Alasia from SELECT in a CASE?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 03/27/2013 :  13:41:59  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
In my SELECT statment I am putting the results from one CASE statment into a Column Alasis which I now need to referance in a different CASE statment. How can I go about doing this? Here is my current code, the Alasis I am trying to reference is called "Ending"

--SELECT LocationName, ProductName, PRoductNumber, (StartingGross WHERE dbo.Terminal.Groos_Gallons_Terminal IS y ELSE StragingNET), dbo.Rcpt_And_Adj.Gross/Net, +
--dbo.Transaions.Gallons/NetGallons = Sales, EndingGroos/EndingNet = End, dbo.Terminal.Groos_Gallons_Terminal = Type, If END > 0 PRING Error

--FROM dbo.DailyInventory
--JOIN dbo.Terminal ON JDELocation
--Join dbo.Rcpt_And_Adj ON JDELocation
--JOIN dbo.Transactions ON JDLocation

--GROUPBY (dbo.DailyInventory.ProductNumber)



SELECT term.Terminal_Name AS Name,
	   di.ProductName + ' (' + di.ProductNumber + ')' AS Product,
	   null AS 'Total Tank Capacity',
	   Beginning = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
			THEN
				di.StartingGrossInv
			ELSE
				di.StartingNetInv
			END,
	   Adjustments = CASE WHEN term.Gross_Gallons_Terminal = 'Y' 
			THEN 
				SUM(
					CASE WHEN ra.Type = 'A'
					THEN
						ra.Gross_Gallons
					ELSE
						0
					END)
			ELSE
				SUM(
					CASE WHEN ra.Type = 'A'
					THEN
						ra.NET_Gallons
					ELSE
						0
					END)
			END,
		Ending = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
			THEN
				di.EndingGrossInv
			ELSE
				di.EndingNetInv
			END,
		Type = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
			THEN
				'Gross'
			ELSE
				'NET'
			END,
		tp.Temp,
		tp.Gravity,
		null AS 'End < Capacity',
		'End > ' = CASE WHEN Ending > 0
		THEN
			'OK'
		ELSE
			'***ERROR***'
		END
FROM dbo.DailyInventory as di
JOIN dbo.Terminal AS term
	ON di.JDELocation = term.JDELocation
JOIN dbo.Terminal_Product AS tp
	ON di.JDELocation = tp.JDELocation
	AND di.ProductNumber = tp.Product_Number
LEFT JOIN dbo.Transactions AS trans
	ON di.JDELocation = trans.JDELocation
LEFT JOIN dbo.Rcpt_And_Adj AS ra
	ON di.JDELocation = ra.JDELocation
	AND trans.Ticket_Number = ra.Ticket_Number
WHERE term.Terminal_ProductionTest = 1
AND tp.Enabled = 'y'
AND di.InventoryDate BETWEEN '03-26-2013' AND GETDATE()
GROUP BY term.Terminal_Name,
		 di.ProductName,
		 di.ProductNumber,
		 term.Gross_Gallons_Terminal,
		 di.StartingGrossInv,
		 di.StartingNetInv,
		 di.EndingGrossInv,
		 di.EndingNetInv


--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

James K
Flowing Fount of Yak Knowledge

3657 Posts

Posted - 03/27/2013 :  14:48:29  Show Profile  Reply with Quote
You can use an alias defined in the select portion of the query only in the order by clause - nowhere else. So your choices are:

a) redefine the entire case expression where you want to use the alias:
....
'End > ' = CASE WHEN 
		CASE WHEN term.Gross_Gallons_Terminal = 'Y'
			THEN
				di.EndingGrossInv
			ELSE
				di.EndingNetInv
			END
	
	 > 0
		THEN
			'OK'
		ELSE
			'***ERROR***'
		END
...


2. Write the query without the 'End > ' column and make that into a subquery. Then you can use the alias.
select *,
'End > ' = CASE WHEN Ending > 0
		THEN
			'OK'
		ELSE
			'***ERROR***'
		END
from
(
	-- your original query without the 'End > ' column here
) s
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