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

2 points:

1) In your example, yes it does keep the NULL attributes. But if you do a JOIN like in my example below, the SECOND file's fields seem to allow NULLS even when the SECOND file has no null fields allowed.


2) If I allow nulls in field FLAG1, and UPDATE file set FLAG2 = 'Y' where FLAG1 = ' ', and FLAG1 is NULL, then it wont get updated properly.

Am I better off testing for space OR NULL, such as UPDATE file set FLAG2 = 'Y' if FLAG1 = space OR null?

I was thinking it was simpler testing for blank, since using DSPPFM or DBU it is tricky to see if a field is blank or null.




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, March 29, 2012 3:01 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?

Mark,

That populates the column as not null, however it doesn't stop it from
being modified to put a null in there with either update or insert.

Joel,

First I wonder why no nulls. They really do have their use.

However, it keeps that property for the existing columns:
CREATE TABLE QTEMP/JOEL (MYCHAR CHAR (5 ) NOT NULL)

INSERT INTO QTEMP/JOEL (MYCHAR) VALUES(null)
Null values not allowed in column or variable MYCHAR.

create table qtemp/joel2 as (
select joel.*, 'hiya' as newcol
from qtemp/joel ) with data

INSERT INTO QTEMP/JOEL2 (MYCHAR) VALUES(null)
Null values not allowed in column or variable NEWCOL.

Just had difficulty with that new column when I tried to prevent nulls
from it.
create table qtemp/joel2 as (
select joel.*, 'hiya' as newcol not null
from qtemp/joel ) with data
Keyword NOT not expected. Valid tokens: , FROM INTO.



Rob Berendt