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

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].