Using Aliases - SQL Server Forums

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

Transact-SQL (2000)

Using Aliases

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-13 : 14:54:46

I'm calculating the field 'TotalAnnualCompensation' by adding a few other fields together but when I try and reference this same field to calculate 'TotalCompensation' I get an error:

Invalid column name 'TotalAnnualCompensation'.

How do I correctly reference this derived field to make another calculation?

SELECT
C.CompID,
C.CompanyName,
C.Ticker,
Ex.ExecutiveID,
Ex.[Name],
Ex.Title,
Ex.Salary,
Ex.Bonus,
Ex.OtherAnnualCompensation,
Ex.RestrictedStock,
Ex.LTIPPayout,
Ex.AllOtherCompensation,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfOptionsExercised,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfExercisableOptions,
ExHoldings.NumberOfUnexercisableOptions,
ExHoldings.ValueOfExercisableOptions,
ExHoldings.ValueOfUnexercisableOptions,

--TotalAnnualCompensation
(Ex.Salary + Ex.Bonus + Ex.OtherAnnualCompensation) AS TotalAnnualCompensation,

--TotalCompensation
(TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized) As TotalCompensation

FROM ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 15:09:48

You'll need to use a derived table:



SELECT 
	CompID,
	CompanyName,
	...,
	TotalAnnualCompensation,
	TotalCompensation
FROM 
(
	SELECT 
	C.CompID, 
	C.CompanyName, 
	C.Ticker,
	Ex.ExecutiveID,
	Ex.[Name],
	Ex.Title,
	Ex.Salary,
	Ex.Bonus,
	Ex.OtherAnnualCompensation,
	Ex.RestrictedStock,
	Ex.LTIPPayout,
	Ex.AllOtherCompensation,
	ExHoldings.OptionsValueRealized,
	ExHoldings.NumberOfOptionsExercised,
	ExHoldings.OptionsValueRealized,
	ExHoldings.NumberOfExercisableOptions,
	ExHoldings.NumberOfUnexercisableOptions,
	ExHoldings.ValueOfExercisableOptions,
	ExHoldings.ValueOfUnexercisableOptions,
	
	--TotalAnnualCompensation
	(Ex.Salary + Ex.Bonus + Ex.OtherAnnualCompensation) AS TotalAnnualCompensation,
	
	--TotalCompensation
	(TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized) As TotalCompensation
	
	FROM ...
) t

Tara

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-07-13 : 15:13:21

You cant refer derived column's alias name into another derived column.

With Regards
Sreenivas Reddy B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 15:14:58

You can if you use a derived table.

Tara

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-07-13 : 15:20:09

Thats fine.

Here, --TotalCompensation
(TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized) As TotalCompensation

this syntax own't work. Please correct me if I am wrong.

With Regards
Sreenivas Reddy B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 15:21:58

But he already knows that as that's why he is getting the error. He wants to know what the workaround is, which is what I provided in my first post.

Tara

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-13 : 15:23:29

I can't seem to get the parens matched up. Is this right? Thanks.

SELECT

C.CompID,
C.CompanyName,
C.Ticker,
Ex.ExecutiveID,
Ex.[Name],
Ex.Title,
Ex.Salary,
Ex.Bonus,
Ex.OtherAnnualCompensation,
Ex.RestrictedStock,
Ex.LTIPPayout,
Ex.AllOtherCompensation,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfOptionsExercised,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfExercisableOptions,
ExHoldings.NumberOfUnexercisableOptions,
ExHoldings.ValueOfExercisableOptions,
ExHoldings.ValueOfUnexercisableOptions,
TotalAnnualCompensation,
TotalCompensation

FROM
(
SELECT
C.CompID,
C.CompanyName,
C.Ticker,
Ex.ExecutiveID,
Ex.[Name],
Ex.Title,
Ex.Salary,
Ex.Bonus,
Ex.OtherAnnualCompensation,
Ex.RestrictedStock,
Ex.LTIPPayout,
Ex.AllOtherCompensation,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfOptionsExercised,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfExercisableOptions,
ExHoldings.NumberOfUnexercisableOptions,
ExHoldings.ValueOfExercisableOptions,
ExHoldings.ValueOfUnexercisableOptions,

--TotalAnnualCompensation
(Ex.Salary + Ex.Bonus + Ex.OtherAnnualCompensation) AS TotalAnnualCompensation,

--TotalCompensation
(TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized) As TotalCompensation

FROM Tcompanies AS C INNER JOIN TExecComp AS Ex ON C.CompID = Ex.CompID
WHERE (((Ex.CompID)=12986))
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 15:26:38

Remove the C., Ex., etc... from the top part as those are no longer valid. They are only valid within the derived table. Also, don't forget the "t" from my query. It is needed to alias the derived table. Refer to my first post to see how it is done again.

Tara

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-07-13 : 15:34:43

Example:

select
empid, empname, Yearlysal, Monthlysal
from
( select
empid, empname, (salary + 100 * 12) as YearlySal ,
(salary + 100 * 12) / 12 as Monthlysal from emp) as X

With Regards
Sreenivas Reddy B

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-13 : 15:46:03

I'm sorry, I'm just not getting it. This is what I have now:

SELECT

CompID,
CompanyName,
Ticker,
ExecutiveID,
[Name],
Title,
Salary,
Bonus,
OtherAnnualCompensation,
RestrictedStock,
LTIPPayout,
AllOtherCompensation,
OptionsValueRealized,
NumberOfOptionsExercised,
OptionsValueRealized,
NumberOfExercisableOptions,
NumberOfUnexercisableOptions,
ValueOfExercisableOptions,
ValueOfUnexercisableOptions,
TotalAnnualCompensation,
TotalCompensation

FROM
(
SELECT
C.CompID,
C.CompanyName,
C.Ticker,
Ex.ExecutiveID,
Ex.[Name],
Ex.Title,
Ex.Salary,
Ex.Bonus,
Ex.OtherAnnualCompensation,
Ex.RestrictedStock,
Ex.LTIPPayout,
Ex.AllOtherCompensation,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfOptionsExercised,
ExHoldings.OptionsValueRealized,
ExHoldings.NumberOfExercisableOptions,
ExHoldings.NumberOfUnexercisableOptions,
ExHoldings.ValueOfExercisableOptions,
ExHoldings.ValueOfUnexercisableOptions,

--TotalAnnualCompensation
(Ex.Salary + Ex.Bonus + Ex.OtherAnnualCompensation) AS TotalAnnualCompensation,

--TotalCompensation
(TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized) As TotalCompensation

FROM (Tcompanies AS C RIGHT JOIN TExecComp AS Ex ON C.CompID = Ex.CompID)
INNER JOIN TExecCompOptionHoldings AS ExHoldings ON Ex.ExecutiveID = ExHoldings.ExecutiveID
WHERE (((Ex.CompID)=12986))) t

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 15:51:16

I don't understand why you have so many parenthesis in your query. Like why 3 opens and closes in your WHERE clause. Don't just add them because you are getting errors. Understand what's going on first.

Here you go:



SELECT 
	CompID, 
	CompanyName, 
	Ticker,
	ExecutiveID,
	[Name],
	Title,
	Salary,
	Bonus,
	OtherAnnualCompensation,
	RestrictedStock,
	LTIPPayout,
	AllOtherCompensation,
	OptionsValueRealized,
	NumberOfOptionsExercised,
	OptionsValueRealized,
	NumberOfExercisableOptions,
	NumberOfUnexercisableOptions,
	ValueOfExercisableOptions,
	ValueOfUnexercisableOptions,
	TotalAnnualCompensation,
	TotalCompensation
FROM 
(
	SELECT 
		C.CompID, 
		C.CompanyName, 
		C.Ticker,
		Ex.ExecutiveID,
		Ex.[Name],
		Ex.Title,
		Ex.Salary,
		Ex.Bonus,
		Ex.OtherAnnualCompensation,
		Ex.RestrictedStock,
		Ex.LTIPPayout,
		Ex.AllOtherCompensation,
		ExHoldings.OptionsValueRealized,
		ExHoldings.NumberOfOptionsExercised,
		ExHoldings.OptionsValueRealized,
		ExHoldings.NumberOfExercisableOptions,
		ExHoldings.NumberOfUnexercisableOptions,
		ExHoldings.ValueOfExercisableOptions,
		ExHoldings.ValueOfUnexercisableOptions,
		Ex.Salary + Ex.Bonus + Ex.OtherAnnualCompensation AS TotalAnnualCompensation,
		TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized As TotalCompensation
	FROM Tcompanies C 
	RIGHT JOIN TExecComp Ex 
	ON C.CompID = Ex.CompID
	INNER JOIN TExecCompOptionHoldings ExHoldings 
	ON Ex.ExecutiveID = ExHoldings.ExecutiveID 
	WHERE Ex.CompID=12986 
) t

The alias t represents the derived table, which I have bolded.

Tara

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-13 : 16:00:42

I appreciate this but I copied your exact code but I get this error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'TotalAnnualCompensation'.

Server: Msg 8156, Level 16, State 1, Line 1
The column 'OptionsValueRealized' was specified multiple times for 't'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 16:01:39

Your problem is here:

TotalAnnualCompensation + Ex.LTIPPayout + Ex.AllOtherCompensation + Ex.RestrictedStock + ExHoldings.OptionsValueRealized As TotalCompensation

I didn't realize that you had snuck in extra code. You can't refer to TotalAnnualCompensation yet. Give me a couple of minutes.

Also you are referring to OptionsValueRealized two times in the derived query and also in the first part of the select. You only want one, right?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 16:06:30

Here you go:



SELECT 
	CompID, 
	CompanyName, 
	Ticker,
	ExecutiveID,
	[Name],
	Title,
	Salary,
	Bonus,
	OtherAnnualCompensation,
	RestrictedStock,
	LTIPPayout,
	AllOtherCompensation,
	OptionsValueRealized,
	NumberOfOptionsExercised,
	NumberOfExercisableOptions,
	NumberOfUnexercisableOptions,
	ValueOfExercisableOptions,
	ValueOfUnexercisableOptions,
	TotalAnnualCompensation,
	TotalAnnualCompensation + LTIPPayout + AllOtherCompensation + RestrictedStock + OptionsValueRealized As TotalCompensation
FROM 
(
	SELECT 
		C.CompID, 
		C.CompanyName, 
		C.Ticker,
		Ex.ExecutiveID,
		Ex.[Name],
		Ex.Title,
		Ex.Salary,
		Ex.Bonus,
		Ex.OtherAnnualCompensation,
		Ex.RestrictedStock,
		Ex.LTIPPayout,
		Ex.AllOtherCompensation,
		ExHoldings.OptionsValueRealized,
		ExHoldings.NumberOfOptionsExercised,
		ExHoldings.NumberOfExercisableOptions,
		ExHoldings.NumberOfUnexercisableOptions,
		ExHoldings.ValueOfExercisableOptions,
		ExHoldings.ValueOfUnexercisableOptions,
		Ex.Salary + Ex.Bonus + Ex.OtherAnnualCompensation AS TotalAnnualCompensation
	FROM Tcompanies C 
	RIGHT JOIN TExecComp Ex 
	ON C.CompID = Ex.CompID
	INNER JOIN TExecCompOptionHoldings ExHoldings 
	ON Ex.ExecutiveID = ExHoldings.ExecutiveID 
	WHERE Ex.CompID=12986 
) t

Do you see the difference?

Tara

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-07-13 : 16:11:15

:-)

Regards
Sreenivas

With Regards
Sreenivas Reddy B

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-13 : 16:13:42

Thank you both very much...that's a cool trick which I'll add to my toolbox.

When in Maine, I'll buy you lobster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources