| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-29-07, 08:17
|
|
Registered User
|
|
Join Date: May 2006
Posts: 82
|
|
|
Result of ALTER TABLE on VIEW
|
|
Hi All,
If we have defined the view as SELECT * FROM BASETABLE and later if we ALTER the basetable to add one or more columns, Will that columns be reflected in VIEW..?
I mean will the view contain those new columns..? Or do we need to drop and recreate the view..?
I have a requirement to alter the base table. As per my knowledge, when we define the view it puts an entry in SYSIBM.SYSCOLUMNS and other catalog tables, and this will be used whenever we access the view. So as a result the added column wont be reflected in VIEW.
I was told that it is not required to alter the view since it is defined as SELECT *.
Ofcourse I will get to know when i alter the table, Do any one have any idea about this meanwhile..?
__________________
Vinay,
|
|
03-29-07, 08:23
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Quote:
|
If we have defined the view as SELECT * FROM BASETABLE and later if we ALTER the basetable to add one or more columns, Will that columns be reflected in VIEW..?
|
No .. The new column will not be included ... IIRC, this is documented in the manuals ..
Sathyaram
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-29-07, 08:29
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 1,279
|
|
|
|
It is considered bad habit to use SELECT * FROM ... (except for ad hoc testing), always specify the columns.
__________________
With kind regards . . . . . SQL Server 2000/2005/2008/2008 R2 Earned beers: 16
Wim
Beware of bugs in the above code; I have only proved it correct, not tried it. -- Donald Knuth
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
|
|
03-30-07, 01:12
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
well * is just just a replacement for long and large no of column names (ahh.. just for LazyDBA's) ;-)
But internally at machine level it stores it with full description and details
__________________
Rahul Singh
Certified DB2 9 DBA / Application Developer
|
|
03-30-07, 01:19
|
|
Registered User
|
|
Join Date: May 2006
Posts: 82
|
|
Thank you everyone..!
I tried this and i do see that view does not contain the new column.
__________________
Vinay,
|
|
03-30-07, 01:20
|
|
Registered User
|
|
Join Date: May 2006
Posts: 82
|
|
I need to drop and re create the view.
__________________
Vinay,
|
|
03-30-07, 07:43
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Well, I wouldn't mind if DBAs (which, I am) are lazy ... I hate extending the privilege to developers though  ...
On a serious note, "select *" is often misleading .. say, you create a view when the table has 9 columns , then add column to the table ... You still have only 9 columns in the view, but looking at 'create view' statement you would assume 10 columns ...
Quote:
|
Originally Posted by rahul_s80
well * is just just a replacement for long and large no of column names (ahh.. just for LazyDBA's) ;-)
But internally at machine level it stores it with full description and details
|
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-30-07, 08:07
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
Well...
if you Know Hindi
DBA - Din Bhar Aaram 
__________________
Rahul Singh
Certified DB2 9 DBA / Application Developer
|
|
03-30-07, 08:43
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Strange it may sound, I can read Hindi but can understand very few words .. 
Lemme guess - Does it mean 'Relaxing all day' ?
Sathyaram
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-30-07, 12:16
|
|
Registered User
|
|
Join Date: May 2006
Posts: 82
|
|
You got it sathya, In other words, ENJOY MAADI -
__________________
Vinay,
|
|
03-30-07, 15:18
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
 My Hindi is not as bad as I believe, then ...
As a thank you gesture for boosting my ego on a Friday evening, here's how I generate select *s ...
Please rememeber to use your own tablename and tableschema instead of ('SYSCAT','SEQUENCES') ... 
On dev environments, I tend to wrap this text into a UDF ...
Code:
with tab(tsch,tname) as
(
values('SYSCAT','SEQUENCES')
),
star(cno,cname) as
(
select -100,'SELECT' from sysibm.sysdummy1
UNION ALL
select colno,(case when colno>0 then ',' else '' end)||colname from syscat.columns,tab where tabschema=tsch and tabname=tname
union all
select 100000,' FROM '||rtrim(tsch)||'.'||rtrim(tname) from tab
)
select cname from star order by cno
To get the output on a single line ..
Code:
with tab(tsch,tname) as
(
values('SYSCAT','SEQUENCES')
),
star(cno,concatcol) as
(
select colno,varchar(colname,1000) from syscat.columns,tab where colno=0 and tabname=tname and tabschema=tsch
union all
select colno,concatcol||','||colname from star s,syscat.columns c, tab where c.colno=cno+1 and tabname=tname and tabschema=tsch)
select 'SELECT '||concatcol||' FROM '||rtrim(tsch)||'.'||rtrim(tname) from star,tab
where cno=(select max(cno) from star)
Have a good weekend everyone
Sathyaram
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Result of ALTER TABLE on VIEW
