Add a column to a view

hello,
I am trying to add a column to a view. This is a generic column not pulling
from the other tables but will be updated based on the values of the other
columns.
I thought I simply altering the view and add a column and set a value, if
the other values were found true. However, it is not allowing me to add a
column unless it is a integer value. Any suggestions?you cannot add a column here.
You use alter view to change the vew definition. You will have to give the
full sql stament with the computed column (if I am not wrong) in the view.
use it this way. Its very similar to create view just the keyword create is
replaced by alter. Hope this helps.
alter view vname
as
select .... col1 + col2 as new_col
from table1
where <conditions>|||> I thought I simply altering the view and add a column and set a value, if
> the other values were found true. However, it is not allowing me to add a
> column unless it is a integer value.
What does "not allowing" mean? Can you show the DDL for the table
referenced in the view, the existing code for the view, the change you are
attempting, and the exact text of the error? A lot of the people here are
pretty smart, but not many are psychic.
A|||Well, I tried to alter the view but it not giving me what I want.
Currently, I am pulling all the columns from other tables. However, I want
to create a new column called columnD and set it to 1 if columnA, columnB an
d
columnC are 1 else set it to 0.
"Omnibuzz" wrote:

> you cannot add a column here.
> You use alter view to change the vew definition. You will have to give the
> full sql stament with the computed column (if I am not wrong) in the view.
> use it this way. Its very similar to create view just the keyword create i
s
> replaced by alter. Hope this helps.
> alter view vname
> as
> select .... col1 + col2 as new_col
> from table1
> where <conditions>|||Are you using Query Analyzer or the view editor in Enterprise Manager? What
code are you trying to run? This sounds like it needs a CASE expression,
and the ability to understand CASE is a serious limitation in Enterprise
Manager. As I asked before, if you can provide *SPECIFIC* information, we
may be able to help.
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
> Well, I tried to alter the view but it not giving me what I want.
> Currently, I am pulling all the columns from other tables. However, I want
> to create a new column called columnD and set it to 1 if columnA, columnB
> and
> columnC are 1 else set it to 0.
> "Omnibuzz" wrote:
>|||>I have it sort of working but it is not totally correct. So i am sing a
> better way in adding a column to the view based on the below criteria.
And I'd like my car to run better! But if I can't be bothered to give my
mechanic more information, he's going to tell me to go jump off a bridge...|||Since you haven't given the defn, I have created a sample. See if this is
what you wnted. Hope this helps.
create table tbl (a int,b int,c int)
go
insert into tbl values (1,1,1)
insert into tbl values (1,1,0)
create view vw1
as
select a,b,c from tbl
select * from vw1
-- till above was your setup...
--now do this
alter view vw1
as
select a,b,c, case when a=b and b=c and c=1 then 1 else 0 end as d
from tbl
select * from vw1|||Initially,
I wrote to add columnD
alter view sam_generic
AS
select columnD, a.columnA, a.columnB,a. columnC, m.member, v.history,
u.category, c.content, l.letter
from tableA AS a INNER JOIN
tableU on a.category = u.category LEFT OUTER JOIN
tableC on a.id = c.id LEFT OUTER JOIN
tableL on a.id = l.id LEFT OUTER JOIN
tableM on a.id = m.id LEFT OUTER JOIN
tableV on a.id =v.id
Set columnD = 1 Where Exists (a.columnA = 1) AND (if a.columnB = 1) AND (if
(a. columnC=1)
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "not allowing" mean? Can you show the DDL for the table
> referenced in the view, the existing code for the view, the change you are
> attempting, and the exact text of the error? A lot of the people here are
> pretty smart, but not many are psychic.
> A
>
>|||I was using query analyzer. I didn't think about using case statements. I'll
try that.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you using Query Analyzer or the view editor in Enterprise Manager? Wh
at
> code are you trying to run? This sounds like it needs a CASE expression,
> and the ability to understand CASE is a serious limitation in Enterprise
> Manager. As I asked before, if you can provide *SPECIFIC* information, we
> may be able to help.
>
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
>
>|||I was submiting the information that you requested. I was working on multipl
e
tasks, so give me a minute to send the information to you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> And I'd like my car to run better! But if I can't be bothered to give my
> mechanic more information, he's going to tell me to go jump off a bridge..
.
>
>