with atc as (select column_id, column_name, 'c.' || column_name as alias, data_type, owner || '.' || table_name as tbl
from all_tab_columns
where table_name = 'S_CONTACT' and owner = 'SIEBEL'
and column_name not in ('CREATED_BY', 'LAST_UPD_BY', 'MODIFICATION_NUM', 'CONFLICT_ID', 'DB_LAST_UPD', 'DB_LAST_UPD_SRC', 'DCKING_NUM')),
atc_ext as (select min(column_id) as min_id, max(column_id) as max_id from atc)
select decode(atc.column_id, atc_ext.min_id, 'select ', '') ||
case when atc.data_type = 'LONG' then '''' || atc.alias || ', ''' -- can't count LONGS so just include them
else 'decode(count(distinct ' || atc.column_name || '), 0, '''', 1, '''', ''' || atc.alias || ', '')'
end
|| decode(atc.column_id, atc_ext.max_id, ' as used_cols from ' || atc.tbl || ';', ' || ') as "-- script"
from atc, atc_ext
order by atc.column_id;with atc as (select column_id, column_name, 'c.' || column_name as alias, data_type, owner || '.' || table_name as tbl
from all_tab_columns
where table_name = 'S_CONTACT' and owner = 'SIEBEL'
and column_name not in ('CREATED_BY', 'LAST_UPD_BY', 'MODIFICATION_NUM', 'CONFLICT_ID', 'DB_LAST_UPD', 'DB_LAST_UPD_SRC', 'DCKING_NUM')),
atc_ext as (select min(column_id) as min_id, max(column_id) as max_id from atc)
select decode(atc.column_id, atc_ext.min_id, 'select ', '') ||
case when atc.data_type = 'LONG' then '''' || atc.alias || ', ''' -- can't count LONGS so just include them
else 'decode(count(distinct ' || atc.column_name || '), 0, '''', 1, '''', ''' || atc.alias || ', '')'
end
|| decode(atc.column_id, atc_ext.max_id, ' as used_cols from ' || atc.tbl || ';', ' || ') as "-- script"
from atc, atc_ext
order by atc.column_id;