select alias use - 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)

select alias use

Author

Topic

erdem12345
Starting Member

8 Posts

Posted - 2008-04-25 : 10:14:45

SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount,Amount*1.2 as [Amount2]
FROM SALES
Invalid column name 'Amount'.

Ms Access is run but SQL 2000 is error.

how alias use ????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-25 : 10:28:46

1
SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount,(QTY*PRICE )*1.2 as [Amount2]
FROM SALES

2
SELECT *,Amount*1.2 as [Amount2] FROM
(
SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount FROM SALES
) AS T

Madhivanan

Failing to plan is Planning to fail

erdem12345
Starting Member

8 Posts

Posted - 2008-04-25 : 12:04:12

thank you Madhivanan 2 number SQL
how multiple alias use ???

example - 1; ( (TAmount+Amount) Amount2 )
SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount,round(Amount*VAT,2) TAmount,(TAmount+Amount) Amount2
FROM SALES

example - 2; ( FMAL+COST1+COST2+COST3 COST )
select C.CODE,C.REPORT
,case when C.REPORT<>0 then round((C.AS*100)/C.REPORT,2) else 0 end as OS
,case when C.REPORT<>0 then round(OS*0.04,2) else 0 end as FCOST
,case when C.REPORT<>0 then round((100*isnull(S.S1,0)/C.REPORT*3.6*77/9000)*4/1000,2) else 0 end as COST1
,case when C.REPORT<>0 then round((100*isnull(S.S2,0)/C.REPORT*3.6*167/9000)*4.5/1000,2) else 0 end as COST2
,case when C.REPORT<>0 then round((100*isnull(S.S3,0)/C.REPORT*3.6*130/9000)*14/1000,2) else 0 end as COST3
,FMAL+COST1+COST2+COST3 COST
from C
left join S on (S.CODE=C.CODE)

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-25 : 12:16:17

You'd have to create a derived table in order to use an alias in the same statement.

What's wrong with this?

SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount, round(QTY * PRICE * VAT,2) as TAmount,((QTY * PRICE * VAT)+(QTY*PRICE) ) as Amount2
FROM SALES

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 12:21:02

quote:
Originally posted by erdem12345

thank you Madhivanan 2 number SQL
how multiple alias use ???

example - 1; ( (TAmount+Amount) Amount2 )
SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount,round(Amount*VAT,2) TAmount,(TAmount+Amount) Amount2
FROM SALES

example - 2; ( FMAL+COST1+COST2+COST3 COST )
select C.CODE,C.REPORT
,case when C.REPORT<>0 then round((C.AS*100)/C.REPORT,2) else 0 end as OS
,case when C.REPORT<>0 then round(OS*0.04,2) else 0 end as FCOST
,case when C.REPORT<>0 then round((100*isnull(S.S1,0)/C.REPORT*3.6*77/9000)*4/1000,2) else 0 end as COST1
,case when C.REPORT<>0 then round((100*isnull(S.S2,0)/C.REPORT*3.6*167/9000)*4.5/1000,2) else 0 end as COST2
,case when C.REPORT<>0 then round((100*isnull(S.S3,0)/C.REPORT*3.6*130/9000)*14/1000,2) else 0 end as COST3
,FMAL+COST1+COST2+COST3 COST
from C
left join S on (S.CODE=C.CODE)

You cant use an alias created on a level directly inside expressions in same level. You either have to repeat the whole operation or go to next level to use as the alias. Thus 1 can be done in two ways:-

either

SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount,round(QTY*PRICE*VAT,2) TAmount,round(QTY*PRICE*VAT,2)+(QTY*PRICE) Amount2 
FROM SALES

where we place actual expression in place of alias or
,round(Amount*VAT,2) TAmount,(TAmount+Amount) Amount2

OR

SELECT temp.Qty,temp.Price,temp.Amount,temp.TAmount,temp.TAmount + temp.Amount AS Amount2
FROM
(
SELECT t.Qty,t.Price,t.Amount,round(t.Amount*VAT,2) TAmount
FROM
(
SELECT QTY as Qty,PRICE as Price,QTY*PRICE as Amount,VAT
FROM SALES
)t
)temp

here we go one step up creating derived tables to get the aliased value directly.
So based on complexity of operation involved you can go for any of approaches.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources