Home » SQL & PL/SQL » SQL & PL/SQL » need help on creating a view excluding some columns from the table
need help on creating a view excluding some columns from the table [message #364479] Mon, 08 December 2008 16:00 Go to next message
aryeman
Messages: 2
Registered: December 2008
Junior Member
hi guys Smile

i am new here and i looking for some help..
can anyone please tell me, how can one exclude columns from a table while creating a view(i know the names of the columns to be excluded)
here is the problem
i have a table having 116 columns.... I need to exclude 3 columns(i know the names of the columns to be excluded) when creating a view.

I can do it as

create a view as select col1,col2,...col113 from table name.

or

is there anything like except or exclude that i can use...

but is there any other way that i can hear fron u guys....

solution for this problem will be very helpful for me ....

thank u
Re: need help on creating a view excluding some columns from the table [message #364481 is a reply to message #364479] Mon, 08 December 2008 16:10 Go to previous messageGo to next message
asmurali
Messages: 2
Registered: December 2008
Location: dallas
Junior Member
You can select all the columns from the table except the columns you don't need by using the following sql

select column_name ||',' from all_tab_columns
where table_name =<your table name> and column_name not in( columns you don't need)

Then simple prefix the results with create a view as select

Hope this helps

Thanks
asm
Re: need help on creating a view excluding some columns from the table [message #364502 is a reply to message #364479] Mon, 08 December 2008 21:23 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

Please use this below query.

for example..

select column_name||','
from cols
where TABLE_NAME='EMPLOYEES'
and column_name not in ('EMPLOYEE_ID','FIRST_NAME','LAST_NAME')

This is a very easy to creating view has 116 columns.


Thanks,
Michael.
Re: need help on creating a view excluding some columns from the table [message #364539 is a reply to message #364502] Tue, 09 December 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does your post add to the previous one?
More it is even not formatted which is no more acceptable for someone with almost 100 posts.

Regards
Michel
Re: need help on creating a view excluding some columns from the table [message #364629 is a reply to message #364479] Tue, 09 December 2008 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle does not support an EXCLUDE or EXCEPT syntax for CREATE VIEW - all the columns to be inclued in the view must be included by name.
Re: need help on creating a view excluding some columns from the table [message #365056 is a reply to message #364479] Wed, 10 December 2008 14:14 Go to previous messageGo to next message
aryeman
Messages: 2
Registered: December 2008
Junior Member
thanks guys for all your help ....can u guys please give any suggestion on how can i get rid of comma after the last column..like after 113 columns(we have 116 columns and we excluded 3 columns
Re: need help on creating a view excluding some columns from the table [message #365079 is a reply to message #365056] Wed, 10 December 2008 21:37 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> select rtrim ('col1,col2,...col112,col113,', ',') from dual;

RTRIM('COL1,COL2,...COL112
--------------------------
col1,col2,...col112,col113

SCOTT@orcl_11g>
Previous Topic: Problem with concatinatnating percentage in the select statement
Next Topic: Parse SQL: How to extract column names, table names from a SQL query
Goto Forum:
  


Current Time: Wed Dec 07 12:47:13 CST 2016

Total time taken to generate the page: 0.14222 seconds