Creating Columns in a 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

General SQL Server Forums

New to SQL Server Programming

Creating Columns in a view

Author

Topic

Brent555
Starting Member

10 Posts

Posted - 2006-05-10 : 23:05:50

Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help

Create View BONUS_PAYOUTS As

Alter Table Employee
Add EMP_BONUS Decimal(6,2)

Update Employee
Set EMP_BONUS = EMP_PCT / 500000

Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS
From Employee
Inner Join Position On Employee.POS_ID = Position.POS_ID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 02:25:54

Why do you want to alter the table thru View?
First alter the table to add column then use view to select data from it

Alter Table Employee
Add EMP_BONUS Decimal(6,2)

Update Employee
Set EMP_BONUS = EMP_PCT / 500000

Create View BONUS_PAYOUTS As
Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS
From Employee
Inner Join Position On Employee.POS_ID = Position.POS_ID

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 03:16:11

I think this is one you want. An additional column EMP_BONUS in your view.

Create View BONUS_PAYOUTS As
Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, 
       convert(decimal(6,2), Employee.EMP_PCT / 500000) as EMP_BONUS
From   Employee Inner Join Position On Employee.POS_ID = Position.POS_ID

Brent555
Starting Member

10 Posts

Posted - 2006-05-11 : 05:56:04

yeah i get no syntax errors and it runs however it isnt computing the bonus from the bonus percent, well it is kinda but getting it way wrong

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 05:57:45

can you post the value of EMP_PCT & EMP_BONUS ?

Brent555
Starting Member

10 Posts

Posted - 2006-05-11 : 06:08:08

I got it, i just had the wrong equation :P
but im still getting it wrong, i think it may be something to do with the join i only get (...) for the employee position name which is text

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources