Adding a column to a view

Question

Post reply

Adding a column to a view

VG-619426

Hall of Fame

Points: 3914
More actions
June 25, 2008 at 11:24 am

#187508

Hi,
I want to add a column to a existing view, but the underlying tables doesn't have that column. This column is going to be a modified_column of a existing column.('clean_name' of column 'name')
How can I achieve this?
Thank you,
-V

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can add calculated columns to views.

create view dbo.CalcCol

as

select Name,

left(Name, 1) as FirstInitial

FirstInitial is a calculated column from Name. Is that the kind of thing you're talking about?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

VG-619426 Hall of Fame Points: 3914 More actions · Answer 2

Actually, I want to create a view first and then I use a stored procedure to update Cleaned_Issuername, by calling a function, which does the clenup for each record from this view.

The intention is to avoiding, creating a column on a production table(tbl_Security), so want to create view to have a subset of data, with a additional column.Since this view is based on tbl_Security here, I can not create a view with out adding column Cleaned_Issuername (having NULLs)to tbl_security.

CREATE VIEW [dbo].[vw_Security_WithCleanedIssuername]

AS

SELECT OrgID, SecurityID, IssuerName, LastModifiedBy, LastModifiedDate, Cleaned_Issuername

FROM dbo.tbl_Security

There could be SSIS solution for these, but I am yet to explore..This is a immediate fix..

Thanks for your feedback.

-V

GSquared SSC Guru Points: 260824 More actions · Answer 3

You can't have a column in a view that isn't in a table, unless the column is just a computed column of some sort.

Views don't hold data that way.

You can add your inline function to the view as a computed column.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

VG-619426 Hall of Fame Points: 3914 More actions · Answer 4

Thanks for your answer again...I think adding a inline function inside a view sounds great!!!

Thanks again!!

Veena