Re: Exclude a field from an SQL View

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.

So, Navigator seems out of the question. I am already using a DSPFFD
outfile to get a list of the fields in PRODTABLE, and am currently building
the PF and LF source specs using that. (The LF specs have all of the
fields of the PF except TRGTMSTAMP.) The "LIKE PRODTABLE" following the
three fields that are defined in every version of the testtrg* files would
save me the trouble of building the PF specs, but if I still need to list
every field in the CREATE VIEW, I'm not gaining anything by switching from
an LF to a view.

FWIW, in case someone asks, all of the production files are defined by PF &
LF DDS specs. We have not started using DDL to define tables and views.

- Dan

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

On 6/2/2016 3:30 PM, Dan wrote:

As far as I can tell from the documentation, I am unable to exclude from

a

View definition just one field from the table on which the view is
created. I.e.:

create view testlib/testtrv001
as select a.* EXCLUDING FIELD123 from testtrg001 a

"EXCLUDING FIELD123" is my pseudocode to indicate that I want all of the
fields in testtrg001 *except* FIELD123 included in the view definition.

I know I can just list all 423 fields (less FIELD123) from the testtrg001
table in the select, but taking a poker in the eye seems preferable.

I'd use the API or Navigator to extract the DDL. That'll give me a list
of column names which I can paste into a CREATE VIEW script - omitting
the one that ought not be there.

--
--buck