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
 General SQL Server Forums
 New to SQL Server Programming
 How to use Column Alasia from SELECT in a CASE?

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-03-27 : 13:41:59
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-27 : 14:48:29
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
   

- Advertisement -