Retrieve all column except 2 coulmn [message #629315] |
Sun, 07 December 2014 00:21 |
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 #629322 is a reply to message #629321] |
Sun, 07 December 2014 08:08 |
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 #629332 is a reply to message #629331] |
Sun, 07 December 2014 12:47 |
|
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 #629392 is a reply to message #629329] |
Mon, 08 December 2014 07:14 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Bilal Khan wrote on Sun, 07 December 2014 12:01yes 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 |
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.
|
|
|