> Interestingly, CTXT is required to convert an integer to text > in > order for SLEN to work. > (CTXT(fieldName)) converts both 0 and -0- (NULL) to 0.
Yep that is what I am experiencing. No solution yet. My personal preference is to fix IFNULL so it will always work. After all, even with ZERO ON, SELECT COUNT(*) FROM TableName WHERE ColumnName IS NULL works just fine, even on REAL values. That's logical. The documentation says ZERO ON makes calculations act as if nulls are zeros. Apparently, it affects a whole lot more, in an undocumented way. Dennis McGrath > > If (CTXT(fieldName)) converted 0 to '0' and -0- to '-0-' or > NULL or > ' ', you could differentiate between them. > > It may be important with set ZERO ON to have NULL operate as > if it > is a zero. However that may not be as critical when converting a > NULL to a > character or text value. > > Randy Peterson > > Dennis McGrath wrote: > > > Razzak, > > > > > What Build of R:BASE 6.5++ you or your client is using? > > > > Client is using 6.5++ 1.866 RT03 > > > > > > > > I got the following results using your example below: > > > > > > -0- is null > > > -0- not gt 0 > > > -0- eq 0 > > > -0- not lt 0 > > > -0- not exists > > > -0- x 0. x > > > > > > 0. is null > > > 0. not gt 0 > > > 0. eq 0 > > > 0. not lt 0 > > > 0. not exists > > > 0. x 0. x > > > > > > > As you can see, NONE of the examples can differntiate between a > null > > and zero. The results are identical! One would logically think > that > > IFNULL would be up to the task, but it is not. > > >
