×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
