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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
424 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

3873 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  
 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.05 seconds. Powered By: Snitz Forums 2000