Solved

How to exclude one column from select statement?

Posted on 2011-02-18
14
1,440 Views
Last Modified: 2012-05-11
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?


0
Comment
Question by:jl66
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 260 total points
ID: 34927472
In sqlplus it's pretty easy:

column col100 noprint

Where are you running the query?
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 34929314
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
 

Author Comment

by:jl66
ID: 34929340
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
 

Author Comment

by:jl66
ID: 34929363
=>Is there any way to deal with it?  It means that "Don't list everything col1,..col99 in select part".
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 260 total points
ID: 34929380
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
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 34929435
If you create the view I mentioned, you can use:

CREATE TABLE t2 SELECT * FROM vwT1

(assuming you name your view vwT1)
0
 
LVL 14

Assisted Solution

by:VBClassicGuy
VBClassicGuy earned 100 total points
ID: 34929464
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jl66
ID: 34931265
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
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34931830
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
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 140 total points
ID: 34931834
^

just a fix for the command

ALTER TABLE table_name SET UNUSED (column_name);
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 140 total points
ID: 34931836
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
 
LVL 1

Expert Comment

by:maheshchari
ID: 34933789
select * from tablename where rownum>=1 and rownum<=99;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34934337
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
 

Author Closing Comment

by:jl66
ID: 34934876
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now