asked on

Calling an alias field to be used in another calculation

We have a couple of complicated calculations being done within the view of SQL but the problem is that i would like to be able to use a field that i have calculated in another calculation into a new calculation.

See an example i have done below

Select FIELD1/FIELD2 as TOTAL COST
If I want to say a new field is total cost + 1 then I have to type in all the above then + 1 instead of
Total cost + 1

Thanks

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sham Haque

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

admoortown

ASKER

Yeah but my idea is that i have a REALLY long calculation being used in the same view and instead of typing it in again to be used for another field, is there a way of using the alias as there is about 10 other calculations going on in this select

Qaiser_Mehmood_Mughal

the other option is that create a view for this query first
Select FIELD1/FIELD2 as TOTAL COST FROM urTable
and then use
SELECT TotalCost +1 as NewCost from view.

admoortown

ASKER

Thanks guys - is there no way to do this within the same view i.e.

SELECT FIELD1/FIELD2 as LENGTH, FIELD3/FIELD4 as WIDTH, LENGTH*WIDTH as AREA

instead of

SELECT FIELD1/FIELD2 as LENGTH, FIELD3/FIELD4 as WIDTH, (FIELD1/FIELD2)*(FIELD3/FIELD4) as AREA

again this is just an example i have about 10 calculations in the one view and all of them are alot longer and more complicated than this

Aneesh

you dont have any other options here

Sham Haque

the ONLY place you can reference a column alias within one SELECT is in the ORDER BY clause....
the easiest method is to reference it in a subquery/derived table fashion as suggested by aneesh and I....or copy/paste....

Scott Pletcher

If the columns are all from one table, for the "first level" of expressions, you could use computed columns to define them. Computed columns can then be referenced just like any other column in a table. Computed columns unfortunately, however, cannot reference previously defined computed columns.
For example:

ALTER TABLE tableName
ADD length AS FIELD1/FIELD2,
width AS FIELD3/FIELD4
--area as LENGTH*WIDTH --<<-- unfortunately this is *not* allowed

Then the view would have this in it:
SELECT ..., length, width, length*width as area, ...

admoortown

ASKER

Thanks guys