473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

select * view and alter table

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
4 6289
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
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
--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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Thomas R. Hummel | last post by:
I am using SQL Server 2000, SP3. I created an updatable partitioned view awhile ago and it has been running smoothly for some time. The partition is on a DATETIME column and it is partitioned by...
9
by: mfyahya | last post by:
Hi, I'm new to databases :) I need help speeding up select queries on my data which are currently taking 4-5 seconds. I set up a single large table of coordinates data with an index on the fields...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
8
by: btober | last post by:
I'm finding that column defaults are not being assigned to nulls when I do an insert by way of a an ON INSERT rule on a view. For example, the following script \set ON_ERROR_STOP ON \c...
14
by: Demetris | last post by:
Hello people! I have a table with more than 30 million rows,a lot of columns and indexes. We need to change a column which is dec(15,2) and nullable to have a default value of zero. As I know you...
33
by: Peter | last post by:
People are telling me it is bad to put select * from <atable> in a view. I better should list all fields of the table inside the definition of the view. I dont know exactly why but some...
3
by: Wes Groleau | last post by:
Never say never..... One of my applications loads a huge amount of data from a text file, sifts through and discards much of it, and rearranges what's left. Finally, it is added to similar data...
0
by: bimeldip | last post by:
Hi, I have managed to create codes to display data from the database in a html page.I have gone on to create a page to allow users to manipulate the table via a html page. For instance users will be...
5
JustJim
by: JustJim | last post by:
I think I've just bumped into the limits of the Having Clause (Help file says 40 expressions, Mary says 99 and I know who I trust). The question is, what happens if you overload the select......
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.