Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    82

    Unanswered: 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,

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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/2012
    Wim

    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

  4. #4
    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

  5. #5
    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,

  6. #6
    Join Date
    May 2006
    Posts
    82
    I need to drop and re create the view.
    Vinay,

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.

  8. #8
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

    Talking

    Well...
    if you Know Hindi
    DBA - Din Bhar Aaram
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.

  10. #10
    Join Date
    May 2006
    Posts
    82
    You got it sathya, In other words, ENJOY MAADI -
    Vinay,

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Cool

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •