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?


jl66Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Selecting in code and create table as select are really two different things.

I don't know of an easy way to do this.

You never did respond to my question:
Where are you running the query?

You can likely 'build' the query dynamically but we need to know your exact requirements.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
In sqlplus it's pretty easy:

column col100 noprint

Where are you running the query?
0
 
VBClassicGuyCommented:
You could either:

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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jl66Author Commented:
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.

0
 
jl66Author Commented:
=>Is there any way to deal with it?  It means that "Don't list everything col1,..col99 in select part".
0
 
VBClassicGuyCommented:
If you create the view I mentioned, you can use:

CREATE TABLE t2 SELECT * FROM vwT1

(assuming you name your view vwT1)
0
 
VBClassicGuyConnect With a Mentor Commented:
Or, go ahead and use your statement of:

CREATE TABLE t2 AS SELECT * FROM t1

then follow it with this statement:

ALTER TABLE t2 DROP COLUMN MyUndesiredField
0
 
jl66Author Commented:
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
0
 
Aaron ShiloChief Database ArchitectCommented:
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.
0
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
^

just a fix for the command

ALTER TABLE table_name SET UNUSED (column_name);
0
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
hi

there is another way.

you could generate a dynamic query using  DBA_TAB_COLUMNS
and select the first 99 columns for the dynamic query.
0
 
maheshchariCommented:
select * from tablename where rownum>=1 and rownum<=99;
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
jl66Author Commented:
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.
0
All Courses

From novice to tech pro — start learning today.