Re: Exclude a field from an SQL View

On 6/2/2016 4:12 PM, Dan wrote:

Thanks Buck. This has to be automated. A CL program would create a table
ala:
RUNSQL ('create table testlib/testtrg001
( TRGTYPE char(1),
TRGTMSTAMP timestamp,
TRGSEQUENC dec(15, 0),
like PRODTABLE ) ')

Following this command, I want to create the view on this table without the
TRGTMSTAMP field. PRODTABLE may be any one of hundreds of production
tables. '001' at the end of the table name and view name represent any
3-digit number.

Ah.

I'd loop through the list of libraries getting a list of tables therein:
select table_name from systables where table_schema = 'QGPL'

Start building a dynamic CREATE VIEW, with the returned table_schema,
and add a column declaration for each column that's in the base table.
I could omit columns that are tagged in a table created for the purpose:

select column_name
from syscolumns
where table_name = :table_name
and table_name not in (
select column_name
from columns_to_be_omitted)

Loop until out of columns for the base table, and finish the dynamic
CREATE VIEW, then prepare and execute it.