HOW TO USE ALIAS ON VIEW - 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

SQL Server Development (2000)

HOW TO USE ALIAS ON VIEW

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-09-29 : 11:12:29

HELLO,
My name is Jenny. Thanks for reading my message.
I have this table (emp) and 1 view (salary_view), I want to create another view based on 'emp' table and 'salary_view' view, my new view is like this:
CREATE VIEW emp_view AS
SELECT emp.emp_id,
salary_view.totalsal AS emp_salary,
(emp_salary + 1000) AS emp_salary_total
FROM emp LEFT JOIN salary_view ON emp.emp_id = salary_view.emp_id
GO

Then, I tested it, it shows this error: "Invalid column name 'emp_salary'.

Can you please help me? this is urgent.

Big thanks.

Jenny.

The stupid question is the question you don't ask.
www.single123.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-29 : 11:19:04

You can't use a derived value from the select in the query

CREATE VIEW emp_view AS
SELECT emp.emp_id,
salary_view.totalsal AS emp_salary,
(salary_view.totalsal + 1000) AS emp_salary_total
FROM emp LEFT JOIN salary_view ON emp.emp_id = salary_view.emp_id
GO

or

CREATE VIEW emp_view AS
(
select emp_id, emp_salary, (emp_salary + 1000) AS emp_salary_total
from
(
SELECT emp.emp_id,
salary_view.totalsal AS emp_salary
FROM emp LEFT JOIN salary_view ON emp.emp_id = salary_view.emp_id
) a
GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 11:19:58

this should do it

CREATE VIEW emp_view AS
SELECT emp.emp_id,
salary_view.totalsal AS emp_salary,
(salary_view.totalsal + 1000) AS emp_salary_total
FROM emp LEFT JOIN salary_view ON emp.emp_id = salary_view.emp_id

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 11:21:30

do...

Corey

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-09-30 : 10:30:45

Woh woh woh..... it worked.
thanks a lot, 7thnite.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-02 : 05:51:59

lol

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources