naming calcualted columns - 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

General SQL Server Forums

New to SQL Server Programming

naming calcualted columns

Author

Topic

wallyk
Starting Member

8 Posts

Posted - 2006-06-19 : 15:33:36

I have several calcuatled columns in a table (see below) that I have been work with. First can I assign a proper naming decision to the columns? How can I reference these columns in other tables?

For example how can I assign names to the columns listed below?

select date, ((abc * .05)+ efgh * .05), (((efgh * .475) + (abc * .475) + (123gross * .05)))
from table
where date = '2006-03-31

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 15:39:47

Just alias the column, then use a derived table (make sure to alias it, notice the t).

SELECT date, Name1, Name2
FROM
(
select date, ((abc * .05)+ efgh * .05) AS Name1, (((efgh * .475) + (abc * .475) + (123gross * .05))) AS Name2
from table
where date = '2006-03-31
) t
WHERE Name1 = SomeValue

Tara Kizer
aka tduggan

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-19 : 15:42:46

Or create derived columns in a table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-06-19 : 15:49:28

Or a VIEW, if you need the calculated values in lots of queries

Kristen

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-19 : 16:14:45

i won't suggest views as they use more resources

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-19 : 16:15:03

I like to vreate views in these cases.
Computed columns are ok, but in some way I feel like I am duplicating the information.

create view vCalc as
select date, ((abc * .05)+ efgh * .05), (((efgh * .475) + (abc * .475) + (123gross * .05))) as calcColumn
from table
go

select date, calcColumn
from vCalc
where date = '20060331' -- ansi date to avoid any kind of ambiguity

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 16:20:02

quote:
Originally posted by sqldev80

i won't suggest views as they use more resources

How do they use more resources?

Tara Kizer
aka tduggan

wallyk
Starting Member

8 Posts

Posted - 2006-06-19 : 16:52:51

I do have to referecne these values in other quereis. How do you reccomend that I create and reference the data from the VIEW statement. I have never used this format.

wallyk
Starting Member

8 Posts

Posted - 2006-06-19 : 16:53:15

quote:
Originally posted by Kristen

Or a VIEW, if you need the calculated values in lots of queries

Kristen

I do have to referecne these values in other quereis. How do you reccomend that I create and reference the data from the VIEW statement. I have never used this format.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 16:59:00

CREATE VIEW SomeView
AS
select date, ((abc * .05)+ efgh * .05) AS ColumnName1, (((efgh * .475) + (abc * .475) + (123gross * .05))) AS ColumnName2
from table
where date = '2006-03-31

Then, you can reference the view instead of the table:

SELECT date, ColumnName1, ColumnName2
FROM SomeView

Tara Kizer
aka tduggan

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-06-19 : 17:11:39

quote:
Originally posted by sqldev80

i won't suggest views as they use more resources

Bovine Excrement

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

wallyk
Starting Member

8 Posts

Posted - 2006-06-19 : 17:25:08

Thanx for the fast reply tara!!!

I tried buliding the following view:

Create view mtdindexmultiplier
as
select date, (100 / agsindex) as agsmult, (100 / meatindex) as meatmult
from scf_mtdindex
where date = 2006-04-28

When I run the query it says that there are no results. Why would this occur? Are there any erros that you see above? Or would I only be able to validagte the results when I integrate the view into another query?

wallyk
Starting Member

8 Posts

Posted - 2006-06-19 : 17:58:57

quote:
Originally posted by tkizer

CREATE VIEW SomeView
AS
select date, ((abc * .05)+ efgh * .05) AS ColumnName1, (((efgh * .475) + (abc * .475) + (123gross * .05))) AS ColumnName2
from table
where date = '2006-03-31

Then, you can reference the view instead of the table:

SELECT date, ColumnName1, ColumnName2
FROM SomeView

Tara Kizer
aka tduggan

I guess more specifically, I need to use these values in other queries. would I use the output generated by the view or refer to it in a qerry?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 18:37:01

The query that you posted just creates the view. It doesn't return anything. You now need to use the view:

SELECT *
FROM mtdindexmultiplier

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-19 : 20:05:37

quote:
Originally posted by wallyk

Thanx for the fast reply tara!!!

I tried buliding the following view:

Create view mtdindexmultiplier
as
select date, (100 / agsindex) as agsmult, (100 / meatindex) as meatmult
from scf_mtdindex
where date = 2006-04-28

When I run the query it says that there are no results. Why would this occur? Are there any erros that you see above? Or would I only be able to validagte the results when I integrate the view into another query?

don't forget to delimit your date with '' :

Create view mtdindexmultiplier
as
select date, (100 / agsindex) as agsmult, (100 / meatindex) as meatmult
from scf_mtdindex
where date = '2006-04-28'

Kristen
Test

22859 Posts

Posted - 2006-06-20 : 00:31:00

You probably want to leave the WHERE bit off your VIEW - so that the VIEW is just there to provide the computed fields:


Create view mtdindexmultiplier
as 
select date, (100 / agsindex) as agsmult, (100 / meatindex) as meatmult -- (1)
from scf_mtdindex

and then you can use


SELECT date, agsmult, meatmult
FROM mtdindexmultiplier
where date = '20060428'

(1) You may way to put ALL the columns from scf_mtdindex in your VIEW so that they are easily accessible in any query you want to make that references the VIEW - without also having to JOIN in the original scf_mtdindex table

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources