Select | Dividing by calculated column

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 | Dividing by calculated column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-21 : 08:38:51

Roman writes "Hello.

This is what I need to accomplish

SELECT field1, (select val from table2) as A, field2/A
FROM table1
WHERE somecondition

The above does not work because second column is only called "A" after the statement is executed. Is there a way to reference second column by its order?

Any suggestions that will help me accomplish my objective are welcome.

Roman."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-21 : 08:47:19

I think you should join the 2 tables table1 & table2,
then all the fields will be readily accessible.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 08:47:30

in order to this you need some kind of relationship between the tables:

SELECT t1.field1, t2.val, t1.field2/t2.val as result
FROM table1 t1
inner join table2 t2 on t1.PK_id = t2.FK_id
WHERE somecondition

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 08:47:54

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-21 : 08:50:41

You are sniped sprit1 !!!

Edit: Darn, tried to make it double sniped, oh well,
time for that typing course...

/rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-21 : 09:18:51

also, in general, if you want to say:

select <some long calculaton> as A, <another calculation> as B, A/B as C
from ...

The easiest was to do this is to use a derived table:


select 
  A,B, A/B as C
from
( select <some long calculaton> as A, <another calculation> as B, A/B as C
  from ...
) tmp

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources