Home » SQL & PL/SQL » SQL & PL/SQL » Exclude column in select (ORACLE 8 or more)
Exclude column in select [message #320103] Wed, 14 May 2008 01:57 Go to next message
ravashingravi
Messages: 7
Registered: March 2008
Location: India
Junior Member
Hi
I want to xclude a column in select

something like

SELECT * EXCLUDE COL4 from my_table;

how to do this
Re: Exclude column in select [message #320105 is a reply to message #320103] Wed, 14 May 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, you have to list all columns.

Regards
Michel
Re: Exclude column in select [message #320146 is a reply to message #320105] Wed, 14 May 2008 03:14 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or, create a view (using Michel's suggestion, by naming all columns you are interested in and leaving 'col4' out of it) and then SELECT * FROM a_view.
Re: Exclude column in select [message #320256 is a reply to message #320103] Wed, 14 May 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
If it's in SQL*Plus, then you can do
column foobar noprint

select * from foobar_table;
icon10.gif  Re: Exclude column in select [message #320284 is a reply to message #320256] Wed, 14 May 2008 09:25 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member


Hi,

Can we do the same generalizing as:

column foobar% noprint -- all columns starting with foobar

select * from foobar_table;

or

column foobar_table.foobar noprint -- only foobar_table.foobar not shown, but foobar from foobar_table1 is shown.

select * from foobar_table;

Thanks,
Priya.

Re: Exclude column in select [message #320297 is a reply to message #320284] Wed, 14 May 2008 10:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's good to be a bit lazy as a developer, this makes sure you go search for reusable bits instead of recoding existing stuff, but you shouldn't overdo it..
Re: Exclude column in select [message #320321 is a reply to message #320103] Wed, 14 May 2008 12:45 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Not sure if this helps, but I wrote something to save me time that builds a column list from USR_TAB_COLUMNS. It's easy enough to exclude certain columns.

Start with:
FOR col_rec IN (SELECT * FROM user_tab_columns WHERE table_name = p_tab_name) LOOP
Re: Exclude column in select [message #320327 is a reply to message #320321] Wed, 14 May 2008 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Not sure if this helps,

I agree with you.

Regards
Michel
icon10.gif  Re: Exclude column in select [message #320380 is a reply to message #320327] Wed, 14 May 2008 22:15 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member



Hi,

Can we say that "The following cannot be achieved even with SQLPLUS":

column foobar% noprint -- all columns starting with foobar

select * from foobar_table;

or

column foobar_table.foobar noprint -- only foobar_table.foobar not shown, but foobar from foobar_table1 is shown.

select * from foobar_table;


Thanks,
Priya.

Re: Exclude column in select [message #320381 is a reply to message #320103] Wed, 14 May 2008 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can we say that "The following cannot be achieved even with SQLPLUS":

We?

The answer might depend upon how clever the solution can be.
icon10.gif  Re: Exclude column in select [message #320395 is a reply to message #320381] Thu, 15 May 2008 00:04 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member

Hi,

Instead of focusing on
The answer might depend upon how clever the solution can be. 

Can you please focus on the find the solution to it. As it might help someone find the solution to their daily activities.

Thanks,
Priya.

Re: Exclude column in select [message #320397 is a reply to message #320103] Thu, 15 May 2008 00:09 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can you please focus on the find the solution to it.
no.
I am not responsible to solve YOUR problems.
Previous Topic: Performance issue
Next Topic: I Have object problem on oracle 9i?
Goto Forum:
  


Current Time: Thu Dec 08 20:10:03 CST 2016

Total time taken to generate the page: 0.07098 seconds