AW: Exclude a field from an SQL View

I'd prefer the GENERATE_SQL() stored procedure in the QSYS2 library over the
QSQGNDDL API.
http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzajq/rzajqprocge
nsql.htm?view=embed


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Charles Wilt
Gesendet: Thursday, 02.6 2016 22:39
An: Midrange Systems Technical Discussion
Betreff: Re: Exclude a field from an SQL View

The functionality used by Navigator to generate the DDL is an IBM provided
API call..

Generate Data Definition Language (QSQGNDDL) API

Charles


On Thu, Jun 2, 2016 at 4:12 PM, Dan <dan27649@xxxxxxxxx> 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.

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.