RE: SQL create a new table by joining two existing PFs - how can I keep attributes of 2nd join file?

I guess I am looking for something equivalent to OPNRYF's "Join with default values . . . . JDFTVAL *NO" option.

Given that it appears that this doesn't exist, I have decided to embrace the NULL stuff and let SQL build each field with ALLOW NULL
- instead of adding gobs of code to suppress this.

Thanks for all the great info & ideas!





-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, March 29, 2012 3:43 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL create a new table by joining two existing PFs - how can I keep attributes of 2nd join file?

Why allow blank? Why not just set to null? Let me see if I can answer
that for myself:

CREATE TABLE rob/JOEL (MYCHAR CHAR ( 5), CONSTRAINT NOBLANKS
CHECK (mychar<>' ' ))

INSERT INTO rob/JOEL (MYCHAR) VALUES('Y')
1 rows inserted in JOEL in ROB.
INSERT INTO rob/JOEL (MYCHAR) VALUES(' ')
INSERT, UPDATE, or MERGE not allowed by CHECK constraint.
INSERT INTO rob/JOEL (MYCHAR) VALUES(null)
1 rows inserted in JOEL in ROB.

DSPPFM QTEMP/JOEL see Y row and empty row. F10=Display hexadecimal makes
empty row 'look' like blanks but
select * from rob/joel where mychar=' '
only returns 'No data selected for output.'

Granted, in RPG you'd have to process "null indicators" on those columns,
or some such logic.
select;
where nullindMYCHAR=*on;
...
where mychar='Y';
...
EndSl;

But, in answer to your question, use OR not AND. ' ' AND null is like
testing for mychar='x' AND mychar='y'.

Rob Berendt