December 7, 2009 at 9:30 am
Is it able to add a column to a view or should we drop the existing view and create new view?
December 7, 2009 at 10:03 am
I belive in SQL 2000 you could use the ALTER VIEW command..
CEWII
December 7, 2009 at 10:48 am
Thanks for the reply.
But is there a command like ALTER VIEW view_name ADD column_name datatype
December 7, 2009 at 11:23 am
No.
ALTER VIEW <ViewName> AS
<Select Statement>
How would add column work? You're saying nothing about which table that column comes from. A view's just a saved select statement, it's got no actual storage like a table does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2009 at 12:20 pm
So i have to alter table first
like ALTER TABLE table_name ADD column_name datatype
Later should i drop the view and create a new view to add that additional column?
December 7, 2009 at 12:34 pm
ALTER VIEW is kind of like a drop and rebuild in that you have to specify the whole SELECT statement that makes up the view. But you can also do a DROP/CREATE, the downside to that is that granted permissions are also dropped.
CEWII
December 7, 2009 at 1:13 pm
thank you
December 8, 2009 at 10:42 am
Please check this syntaxes(NOT ONLY SYTAXES BUT A CODE TO RUN FAST)
Is this syntax correct
I want to add column to a table and set default value for that column to be 'N'
ALTER TABLE DWT00050_IMC_CCYYMM_FACT
ADD COLUMN CONTRB_DISTB_FLG char(1) {SET DEFAULT 'N'}
After altering table i have to alter view. I have to add new column to view
Please check the syntax
ALTER VIEW view_name
("column 1", "col 2", "col3", "col4")
SELECT ("col5", "col6", "col7", "col8")
FROM table_name
December 8, 2009 at 10:54 am
rahulsony111 (12/8/2009)
ALTER TABLE DWT00050_IMC_CCYYMM_FACTADD COLUMN CONTRB_DISTB_FLG char(1) {SET DEFAULT 'N'}
Nope. Invalid syntax there. Check Books Online for the syntax of an ALTER TABLE ADD ... statement
ALTER VIEW view_name
("column 1", "col 2", "col3", "col4")
SELECT ("col5", "col6", "col7", "col8")
FROM table_name
Completely incorrect, that'll never run. The select statement that you've given there isn't even correct.
I suggest you look in Books online both for the basic syntax of a SELECT statement and for the syntax for CREATE/ALTER VIEW.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 11:29 am
How about this code
ALTER TABLE DWT00050_IMC_CCYYMM_FACT
ADD CONTRB_DISTB_FLG char(1) [DEFAULT 0]
ALTER VIEW view_name
AS
SELECT ("col1", "col2", "col3", "col4")
FROM table_name
December 8, 2009 at 12:12 pm
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DEFAULT 0'.
Msg 111, Level 15, State 1, Line 4
'ALTER VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.
You know, it would be a hell of a lot faster and a hell of a lot easier for you to open query analyser, paste the code in there and run a syntax check rather than pasting the code into SSC, me then going and opening management studio, pasting the code in, running a syntax check, copying the errors out and posting them on SSC.
That's still not a valid select statement in the view. You do know how to write basic SQL, don't you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 12:25 pm
What kind of SQL is that? I've never seen that flavor. I'm with G, do you have any experience?
CEWII
December 8, 2009 at 12:32 pm
I am sorry if i make you guys frustrated.
I am seeing online and writing those queries, even i am testing them but i am getting errors.
I am just asking if anyone knows correct syntax.
Anyways thanks for your concern.
December 8, 2009 at 12:43 pm
rahulsony111 (12/8/2009)
I am seeing online and writing those queries, even i am testing them but i am getting errors.I am just asking if anyone knows correct syntax.
Sure, I know the correct syntax but, quite honestly, you need to learn this. Did you look in Books Online? (Books Online being the SQL help file that comes with SQL. Accessed by pressing F1 while in query analyser or available online at http://msdn.microsoft.com/en-us/library/dd631854(SQL.10).aspx
SELECT: http://msdn.microsoft.com/en-us/library/aa259187%28SQL.80%29.aspx
ALTER TABLE: http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx
ALTER VIEW: http://msdn.microsoft.com/en-us/library/aa275460%28SQL.80%29.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 2:42 pm
I think i got it
ALTER TABLE DWSODS01.DWT00301_ORD_DTL_OMS
ADD (COMB_ORD_FLG NUMBER(5,0) NOT NULL);
ALTER TABLE DWSODS01.DWT00301_ORD_DTL_OMS
MODIFY COLUMN COMB_ORD_FLG DEFAULT 0;
I think this one is correct.
But the problem is i am altering two times here.
Is there a way to write the whole thing only once
And for a guy like me its tough to see the library and write the code. I always look for examples and replace my stuff with that
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply