Hi Joel,

I didn't go back to see the beginning of this thread, which (having read this over a couple of days) I've already forgotten, so I'm not sure if this solves your problem:

CREATE TABLE QTEMP/JOEL (
FIELD1 CHAR(5) NOT NULL WITH DEFAULT,
FIELD2 CHAR(2) NOT NULL WITH DEFAULT)

INSERT INTO QTEMP/JOEL (FIELD1) VALUES('A')

FIELD2 will contain the default for a character field, i.e. blanks.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx> /

On 3/29/2012 1:50 PM, Stone, Joel wrote:
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

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].