Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve all column except 2 coulmn (11g, Window 8)
Retrieve all column except 2 coulmn [message #629315] Sun, 07 December 2014 00:21 Go to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Hello Experts.
i have table vote containing more then 15 attributes. i.e Vote(handicapped_infants, water_project_cost, adoption_of_budget, physician_fee_freeze,el_salvador_aid,religious_groups, anti_satellite, aid_to_nicaraguan, mx-missile, immigration,synfuels, education_spending, crime, duty_free_exports, Class).
i want to retrieve the field values of all column using SELECT statement except "anti_satellite" and "synfuels". is there any way to instead of writing the all column name in SELECT statement, we use "anti_satellite" and "synfuels" coulmn name and retrieve all other field values except these two column.

Regards.
Re: Retrieve all column except 2 coulmn [message #629316 is a reply to message #629315] Sun, 07 December 2014 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No.

Re: Retrieve all column except 2 coulmn [message #629319 is a reply to message #629316] Sun, 07 December 2014 03:04 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi,

As far as I know, you can not do that in a single SQL statement.

But if there is very good reason (that I cant think of) which makes this request valid, you may use:
SELECT Column_Name
FROM   ALL_TAB_COLUMNS
WHERE  Table_name
AND column_name NOT IN ( 'names of the fields you need to exclude' )


and then use an oracle function to form the select that you want and execute it.

Thanks,
Ferro
Re: Retrieve all column except 2 coulmn [message #629321 is a reply to message #629319] Sun, 07 December 2014 08:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP, How much time and effort would it really need to mention 13 column names out of 15 columns in a table? I can do it in couple of seconds, ok, at least in less than a minute using a good editor.
Re: Retrieve all column except 2 coulmn [message #629322 is a reply to message #629321] Sun, 07 December 2014 08:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OraFerro, no objection to your suggestion, but by the time you write that query, the columns required for the select list could be easily written. And no overhead to query all_tab_columns too. Isn't it?
Re: Retrieve all column except 2 coulmn [message #629329 is a reply to message #629322] Sun, 07 December 2014 12:01 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
yes you are right, but sometime we have hundreds of column and we have to left only few of them and write all others, such cases we need of that. Thanks for your useful suggestion...
Re: Retrieve all column except 2 coulmn [message #629330 is a reply to message #629329] Sun, 07 December 2014 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what about using the script file you have to create the table?
If you have an error on the table in the database or you take the wrong or ... you propagate the error in your code instead of finding it when you'll try to compile the code with the correct columns.
Remember: the database dictionary is NOT a source control point.

Re: Retrieve all column except 2 coulmn [message #629331 is a reply to message #629330] Sun, 07 December 2014 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>but sometime we have hundreds of column

I suspect that any such table is NOT normalized, but has column names which are really application data

Keep in mind that all data is really stored in BLOCKS; which means all columns are actually brought into RAM regardless of how many exist in SELECT clause.
Little, if anything, is save by having a few columns not in the SELECT clause.
Re: Retrieve all column except 2 coulmn [message #629332 is a reply to message #629331] Sun, 07 December 2014 12:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you could create a view (as "SELECT col1, col2, ..., col13 from your_table", leaving out these two columns you don't need. Then you'd "SELECT * from view" which would, effectively, do what you're asking. In my opinion, "SELECT *" should be avoided anywhere except for quick GUI selects (when you want to quickly see what's in there), but anywhere else I'd much rather use column names and name them all, one by one, in any statement you use.
Re: Retrieve all column except 2 coulmn [message #629333 is a reply to message #629332] Sun, 07 December 2014 12:59 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Thanks to all expert for useful suggestion....
Re: Retrieve all column except 2 coulmn [message #629392 is a reply to message #629329] Mon, 08 December 2014 07:14 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Bilal Khan wrote on Sun, 07 December 2014 12:01
yes you are right, but sometime we have hundreds of column and we have to left only few of them and write all others, such cases we need of that. Thanks for your useful suggestion...


Writing code. It's what developers DO. By using the features of any competent text editor, I could knock that out in about a minute, regardless of whether the table has 5 columns or 1000 columns.

sql> spool myquery.sql
sql> select column_name || ',' from dba_tab_cols where owner='MYSCHEMA' and table_name='MYTABLE'
sql> spool off
sql> exit

Then simply open myquery.sql, and add the finishing touches - delete the columns you don't want, add the SELECT, FROM and WHERE statements, whatever else is needed
Re: Retrieve all column except 2 coulmn [message #629393 is a reply to message #629392] Mon, 08 December 2014 07:23 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
And also GUI's let us directly copy the column names directly from the output window, thus even spool isn't required. Just need to paste it on any nice editor and remove unwanted columns. Just another option if using any GUI tool.
Previous Topic: how to update values using trigger
Next Topic: Getting data from data dictionary
Goto Forum:
  


Current Time: Fri Mar 29 03:32:42 CDT 2024