selecting every column except some.

[bcdixit]

Is there a way I can select all but a couple of columns in a select query.

Basically I want to do some thing like..

select (* -(DW_INSERT_DTTM,DW_INSERT_DTTM)) from tableA
MINUS
select (* -(DW_INSERT_DTTM,DW_INSERT_DTTM)) from tableB

and vice versa
Its easy to do a minus of two tables when the number of columns in a table are small but it gets really time consuming when columns are plenty. Also the dw_insert_dttm and dw_update_dttm times for tableA are different compared to tableB

Does anybody knows a workaround?

Thanks in advance.
bcd

 

[dnoeth]

Hi bcd,
something like "select * but (col1, col2)"?

I've heard that request several times, would be nice to have, but it's not part of Standard SQL...

If you don't want to key in all the columns, retrieve that infomation from the system tables:

select trim(columnname) || ','
from dbc.columns
where databasename = 'dbc' and tablename = 'tables'
order by columnid

Then you simply have to remove those unwanted columns.

Or use an database editor with a builtin feature to create a column list (using ODBC/JDBC).

Dieter

 

[bcdixit]

I hope they have something like this included in the standard.

Thanks for your help though,
bcd