Calculated fields from another calculated field??

[jtrembla]

In MS Access you can reference a calculated fields by the alias name: example:

SELECT [sales]/[unit] AS UnitSales, [UnitSales]-1 AS UnitSalesLessOne
FROM Table1;


Can this be done in t-sql, I get an invalid field name error?
If not then that is crappooo

 

[gmmastros]

If not then that is crappooo

I guess it's crappoooey then.

There are some things you could do, but mostly they're just workarounds. The simple thing is to repeat the calculation.

You could also use a derived table for this, or use computed columns.

Derived Table:

[tt][blue]
Select UnitSales, UnitSales-1 As UnitSalesLessOne
From (
Select [sales]/[unit] As UnitSales
From Table1
) As AliasName
[/blue][/tt]

The computed column approach.

[tt][blue]
Alter Table Table1 Add UnitSales As [sales]/[unit]
Alter Table Table1 Add UnitSalesLessOne As ([sales]/[unit]) -1
[/blue][/tt]

Now, when you select data from this table, there will be 2 new columns. Each time the data is accessed, the numbers will be calculated, so updates to the table will automatically update the computed column.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jtrembla]

thanks, it just seems like a simple update to the t-sql language to allow this. I mean if MS Access allows this, then t-sql certainly can, repeating the columns in calculations can be quite annoying and long, especially when I have like 5 calculated columns referencing other calculated columns.

I think I am just going to add the calculated columns via client side in my application.

 

[Qik3Coder]

jtrembla,

Access is a totally different beast than SQL.
Access is 1/2 application dev, 1/2 db, this is something to keep in mind when you say "well access can do this"

Another workaround to your problem, without directly answering your question:

You can create a table function that maps over your table.
You can have all your number logic in there.
Whenever you access your table, do it through the table function.

Your:
Select * from myTable mt
becomes
Select * from dbo.udfGetMyTable() mt

Thanks,


If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]