How to exclude one column from select statement?
Have a table with 100 columns and want to show 99 columns. Easy way is
select col1, col2, ...col99
from t1;
That will be multiple pages only for select part. but if doing the following
select * from t1;
That will show everything.
Gurus, Is there any easy way to solve my issue?
select col1, col2, ...col99
from t1;
That will be multiple pages only for select part. but if doing the following
select * from t1;
That will show everything.
Gurus, Is there any easy way to solve my issue?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much. The point should be awarded to you now. However, the purpose for it is as follows:
create table t2 as
select * from t1;
in t2 it only needs col1....col99. Is there any way to deal with it?
If you need a new ticket, I can open a new one. Sorry for that.
create table t2 as
select * from t1;
in t2 it only needs col1....col99. Is there any way to deal with it?
If you need a new ticket, I can open a new one. Sorry for that.
ASKER
=>Is there any way to deal with it? It means that "Don't list everything col1,..col99 in select part".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you create the view I mentioned, you can use:
CREATE TABLE t2 SELECT * FROM vwT1
(assuming you name your view vwT1)
CREATE TABLE t2 SELECT * FROM vwT1
(assuming you name your view vwT1)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If the table has over 100 Millions rows, adding one column and dropping it would be costly. Is there any better way to deal with it
hi
no need to drop the column.
use the UNUSED command
alter table alter column set unused.
this is a Datadict command and works immediatly no overhead.
no need to drop the column.
use the UNUSED command
alter table alter column set unused.
this is a Datadict command and works immediatly no overhead.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select * from tablename where rownum>=1 and rownum<=99;
What are you wanting to do?
Are you wanting to select columns or remove the column permanently from the table?
ashilo,
I already mentioned dynamic sql but thanks for providing the view.
Are you wanting to select columns or remove the column permanently from the table?
ashilo,
I already mentioned dynamic sql but thanks for providing the view.
ASKER
Thank for everyone. Dynamically build the query may be the practical way to work out the issue. Special thanks to slightwv and ashilo. It does not seem matter. I can use dba_tab_columns or user_..., depending on the user privilege logged in to form the query.
1. Select all fields and just ignore the unwanted one in code.
2. Build a view which selects all columns except the unwanted one, and then select from the view rather than the table.