Cant use column alias in query

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 2005 Forums

Transact-SQL (2005)

Cant use column alias in query

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-01-13 : 09:15:33

I've got this view


SELECT     TOP (100) PERCENT VehicleRef, CapID, Type, Manufacturer, Model, Derivative, AdditionalFreeText, Created, Updated, Details, Notes, Dealer, Discount, 
                      DealerContribution, DeliveryCharge, RegisteredStatus, RegistrationDate, MustBeOrderedBy, MustBeDeliveredBy, Source, VRB, ApproxLeadTime, 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 1 THEN CH ELSE NULL END) AS [2Year10kMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 0 THEN CH ELSE NULL END) AS [2Year10kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 1 THEN CH ELSE NULL END) AS [2Year20kMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 0 THEN CH ELSE NULL END) AS [2Year20kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 1 THEN CH ELSE NULL END) AS [2Year30kMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 0 THEN CH ELSE NULL END) AS [2Year30kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 1 THEN CH ELSE NULL END) AS [3Year10kMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 0 THEN CH ELSE NULL END) AS [3Year10kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 1 THEN CH ELSE NULL END) AS [3Year20kMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 0 THEN CH ELSE NULL END) AS [3Year20kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 1 THEN CH ELSE NULL END) AS [3Year30kMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 0 THEN CH ELSE NULL END) AS [3Year30kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [2Year10kMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [2Year10kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [2Year20kMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [2Year20kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [2Year30kMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [2Year30kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [3Year10kMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [3Year10kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [3Year20kMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [3Year20kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [3Year30kMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [3Year30kNotMaintainedPCH]
FROM         dbo.vwAllMatrixVehiclesGENUS
WHERE     (GVSLive = 1) AND (WebOffer = 0 OR
                      WebOffer IS NULL)
GROUP BY VehicleRef, CapID, Type, Manufacturer, Model, Derivative, AdditionalFreeText, Created, Updated, Details, Notes, Dealer, Discount, DealerContribution,
                       DeliveryCharge, RegisteredStatus, RegistrationDate, MustBeOrderedBy, MustBeDeliveredBy, Source, VRB, ApproxLeadTime
ORDER BY VehicleRef

It works fine, but now I wanted to add this extra column

MAX([2Year10kMaintainedPCH] / 1.15) AS Expr1

It gives me this error

Invalid column name 2Year10kMaintainedPCH

How can I do it?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 09:20:41

something like this


SELECT     TOP (100) PERCENT *,[2Year10kMaintainedPCH] / 1.15 AS Expr1
FROM
(
SELECT VehicleRef, CapID, Type, Manufacturer, Model, Derivative, AdditionalFreeText, Created, Updated, Details, Notes, Dealer, Discount, 
                      DealerContribution, DeliveryCharge, RegisteredStatus, RegistrationDate, MustBeOrderedBy, MustBeDeliveredBy, Source, VRB, ApproxLeadTime, 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 1 THEN CH ELSE NULL END) AS [2Year10kMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 0 THEN CH ELSE NULL END) AS [2Year10kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 1 THEN CH ELSE NULL END) AS [2Year20kMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 0 THEN CH ELSE NULL END) AS [2Year20kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 1 THEN CH ELSE NULL END) AS [2Year30kMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 0 THEN CH ELSE NULL END) AS [2Year30kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 1 THEN CH ELSE NULL END) AS [3Year10kMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 0 THEN CH ELSE NULL END) AS [3Year10kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 1 THEN CH ELSE NULL END) AS [3Year20kMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 0 THEN CH ELSE NULL END) AS [3Year20kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 1 THEN CH ELSE NULL END) AS [3Year30kMaintainedCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 0 THEN CH ELSE NULL END) AS [3Year30kNotMaintainedCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [2Year10kMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 10000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [2Year10kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [2Year20kMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 20000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [2Year20kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [2Year30kMaintainedPCH], 
                      MAX(CASE WHEN Term = 2 AND MilesPA = 30000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [2Year30kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [3Year10kMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 10000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [3Year10kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [3Year20kMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 20000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [3Year20kNotMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 1 THEN PCH ELSE NULL END) AS [3Year30kMaintainedPCH], 
                      MAX(CASE WHEN Term = 3 AND MilesPA = 30000 AND Maintained = 0 THEN PCH ELSE NULL END) AS [3Year30kNotMaintainedPCH]
FROM         dbo.vwAllMatrixVehiclesGENUS
WHERE     (GVSLive = 1) AND (WebOffer = 0 OR
                      WebOffer IS NULL)
GROUP BY VehicleRef, CapID, Type, Manufacturer, Model, Derivative, AdditionalFreeText, Created, Updated, Details, Notes, Dealer, Discount, DealerContribution,
                       DeliveryCharge, RegisteredStatus, RegistrationDate, MustBeOrderedBy, MustBeDeliveredBy, Source, VRB, ApproxLeadTime
)t
ORDER BY VehicleRef

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources