Code works in Access but not in SQL Server

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

Transact-SQL (2008)

Code works in Access but not in SQL Server

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-12-12 : 12:36:23

Hi experts,

This code below works very well in Access:

SELECT DISTINCT c.carTypes, h.hourly, h.hourly * 
            SWITCH([CarTypes] = 'Sedan', 55
                  ,[CarTypes] = 'Stretch Limo : 6 Passenger', 75
                  ,[CarTypes] = 'Stretch Limo : 8 Passenger', 90
                  ,[CarTypes] = 'Stretch Limo : 10  Passenger', 95
                  ,[CarTypes] = 'Stretch Limo : 12  Passenger', 110
                  ,[CarTypes] = 'SportUtilityVehicle', 110
                  )  AS Fare
            , Fare*10/100 AS Discount
            , Fare-Discount AS NewFare
            , NewFare*7/100 AS Tax
            , NewFare*20/100 AS Tip
            , NewFare+Tax+TIP AS Total
FROM HourlyRates AS h INNER JOIN carType AS c ON h.ID= c.hourlyID;

However, when I try to run what I call the equivalence in sql server, I get errors:

SELECT DISTINCT c.carTypes, h.hourly, h.hourly *
(CASE [CarTypes]
     WHEN 'Sedan' Then 55
     WHEN 'Stretch Limo : 6 Passenger' Then 75
     WHEN 'Stretch Limo : 8 Passenger' Then 90
     WHEN 'Stretch Limo : 10  Passenger' Then 95
     WHEN 'Stretch Limo : 12  Passenger' Then 110
     WHEN 'SportUtilityVehicle' Then 110
 ELSE '' END) As Fare
            , Fare*10/100 AS Discount
            , Fare-Discount AS NewFare
            , NewFare*7/100 AS Tax
            , NewFare*20/100 AS Tip
            , NewFare+Tax+TIP AS Total
 FROM HourlyRates AS h INNER JOIN carType AS c ON h.ID= c.hourlyID;

The error says following are invalid colum names:

, Fare*10/100 AS Discount
, Fare-Discount AS NewFare
, NewFare*7/100 AS Tax
, NewFare*20/100 AS Tip
, NewFare+Tax+TIP AS Total

Any ideas?

Thanks in advance

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-12-12 : 12:42:48

You cannot use Fare in calculations the way you have within T-SQL.
You might look ate CTE's (Common Table Expressions) - If I had more time I would show an example.

djj

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-12-12 : 13:02:59

Thank you very much

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 15:05:56

quote:
Originally posted by simflex

Thank you very much

You can do this with cross apply (untested):


SELECT DISTINCT c.carTypes, h.hourly, h.hourly
            , cur.Fare*10/100 AS Discount
            , new.fare AS NewFare
            , new.fare*7/100 AS Tax
            , new.fare*20/100 AS Tip
            , new.fare+Tax+TIP AS Total
 FROM HourlyRates AS h INNER JOIN carType AS c ON h.ID= c.hourlyID
 cross apply 
 (select CASE [CarTypes]
     WHEN 'Sedan' Then 55
     WHEN 'Stretch Limo : 6 Passenger' Then 75
     WHEN 'Stretch Limo : 8 Passenger' Then 90
     WHEN 'Stretch Limo : 10  Passenger' Then 95
     WHEN 'Stretch Limo : 12  Passenger' Then 110
     WHEN 'SportUtilityVehicle' Then 110
 ELSE '' END) As cur(fare)
 cross apply (select cur.fare-cur.Fare*10/100) as new(fare)

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-12-12 : 15:09:45

Thank you very much

I was able to do it with cte.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources