SELECT all rows except those with computed values

[gchg]

Is there a way to SELECT all the columns in a Table except those with computed values?

[chris_keating]

I do not think there is a direct way, but you could construct the query and then run it.  You can use the LIST method to create the select list.  Here is an example (rough implementation but shows the idea).

create or replace table t (
	id bigint not null default autoincrement,
	c1 bigint null default 'test',
	c2 bigint null compute( 5* c1 ),
	primary key ( id asc )
);
insert into t( c1 ) values ( 5 );
select * from t;

create or replace procedure runQueryWithSkipComputedCol( in @ownername char(128), in @tablename char(128) )
begin
    declare @stmt long varchar;

    select
        'select ' +
        list( column_name, ' ,' ) + ' ' + ' from ' +
        su.user_name + '.' +
        st.table_name
    into
        @stmt
    from 
        systabcol stc join 
            systable st join 
                sysuser su 
    where
            su.user_name = @ownername
        and st.table_name = @tablename
        and stc.column_type <> 'c'
    group by st.table_name, su.user_name;
    execute immediate with result set on @stmt;
end;

call runQueryWithSkipComputedCol( 'dba', 't' );

[VolkerBarth]

I'd suggest to add an ORDER BY to the LIST aggregate, such as ...list( column_name, ', ' , order by column_id)...

[chris_keating]

Thanks Volker. That is a great improvement.