Re: Exclude a field from an SQL View

Bloody brilliant. Using an SQL select to generate a list of fields inside
the CREATE VIEW statement. Is it possible to know everything there is to
know about SQL? (I'm pretty sure the answer is no.)

I will give this a try tomorrow. Thanks Buck!

- Dan

On Thu, Jun 2, 2016 at 4:43 PM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

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.

--
--buck