By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,903 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,903 IT Pros & Developers. It's quick & easy.

select * view and alter table

P: n/a
I have a view defined as select * on base table. When base table is
altered to add new column, new column does not appear when selected
from view. Here is what I did to test.

db2 "create table test_sch.test ( c1 smallint, c2 smallint)"
db2 "insert into test_sch.test values(1,1)"
db2 "insert into test_sch.test values(2,2)"

db2 "create view test_sch.test_v as select * from test_sch.test"

db2 "select * from test_sch.test_v"

C1 C2
------ ------
1 1
2 2

2 record(s) selected.
db2 "alter table test_sch.test add c3 smallint "

db2 "select * from test_sch.test"

C1 C2 C3
------ ------ ------
1 1 -
2 2 -

2 record(s) selected.

db2 "select * from test_sch.test_v"

C1 C2
------ ------
1 1
2 2

2 record(s) selected.

As the view is defined as select * from test_sch.test,
I was expecting select * from test_sch.test_v view to return C3 also,
but it didn't. Any idea why?

I had to drop the view and recreate it to see c3. I am curious to know
why it did not take c3 when select * is used in view definition.

Thanks for your time.
Manoj
Mar 11 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Dave Hughes wrote:
Anyway, I suspect this is one of the reasons that "SELECT *" is
considered by some to be a horrible mistake :-)
Not only "considered" - it is a horrible mistake. ;-)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Mar 12 '08 #2

P: n/a
A bit off topic, but has anyone thought to add a function key to an
SQL editor that will expand a "SELECT *" or "INSERT INTO" with the
full column list?
Mar 12 '08 #3

P: n/a
--CELKO-- wrote:
A bit off topic, but has anyone thought to add a function key to an
SQL editor that will expand a "SELECT *" or "INSERT INTO" with the
full column list?
Data Studio has a lot of inteliSense technology. More importantly it
does such things in pureQuery (LINQ in Java)
Whether it has an "all columns" options I don't know.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 12 '08 #4

P: n/a
aka

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:63*************@mid.individual.net...
--CELKO-- wrote:
>A bit off topic, but has anyone thought to add a function key to an
SQL editor that will expand a "SELECT *" or "INSERT INTO" with the
full column list?
Toad for DB2 can do, even in the freeware edition
Data Studio has a lot of inteliSense technology. More importantly it does
such things in pureQuery (LINQ in Java)
Whether it has an "all columns" options I don't know.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 13 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.