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

On 29-Mar-2012 13:16 , Stone, Joel wrote:

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

Presumably "or" was meant, versus "and" in the above, as is alluded in later quoted text\comment? The FLAG1 can not be both, because a value of blank and the NULL value [meaning effectively, not-a-value] are mutually exclusive. But consider:

UPDATE file set ...
WHERE ifnull(FLAG1,' ')=' '

The same IFNULL, COALESCE, or VALUE [the latter two are synonyms] as discussed in prior messages, can be used there was well.

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

Yes, as in the above predicate, or as in the following:

UPDATE file set ...
WHERE (FLAG1=' ' OR FLAG1 IS NULL)

FWiW there is another predicate that is sometimes useful to replace a similar (tst OR tstNULL); e.g. to select all rows WHERE (FLAG1<>' ' OR FLAG1 IS NULL), the following [DISTINCT predicate] can be used instead:

WHERE FLAG1 IS DISTINCT FROM ' '

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.

The DSPPFM utility presents the type-specific default for the null value; e.g. 0x00 for integer\binary, 0xF0 for zoned BCD, 0x00 and\or last\sign byte of 0x0F for packed BCD, 0x40 for EBCDIC char, 0x20 for ASCII char, etc. Thus if FLAG1 is CHAR, then a search using either of x'40' or ' ' for locating within the buffer position representing the FLAG1 data, will find a row for either the database NULL value or for space\blank... with no means to distinguish which :-(

Regards, Chuck