As mentioned earlier, the characteristic functions developed in [2] are ideal candidates for
macro definition. However, the _eqn macro (and all of its
cousins _gen, _ltn, _len, _nen and _gtn still leave us with the
issue of how to deal with null values. In [3], the authors
make their position clear that characteristic functions (and by
implication, macros) should not behave any differently than straight
T-SQL. As such, if a macro such as _eqn is required to return
some value other null when comparing two arguments, one of
which is null then this special handling should be done with
the isnull() built-in function and passed as an argument to
the macro. Thus
_eqn(isnull(A,0),3)
Boolean operators such as and and or are also definable using macros. These
operators are normally binary but since an m4 macro can have up to 9 arguments these
macros can be made n-ary ( n <=9) with little extra effort. They are only applicable to
arithmetic expressions such as other characteristic functions. For example,
define(_and,`sign(abs( $1 * $2 ifelse($3,,,* $3 )
ifelse($4,,,* $4 ) ifelse($5,,,* $5 ) ifelse($6,,,* $6 )))')
and
define(_or,`sign(abs( $1 + $2 ifelse($3,,,+ $3 )
ifelse($4,,,+ $4 ) ifelse($5,,,+ $5 ) ifelse($6,,,+ $6 )))')
define _and and _or macros which handle up to six
arguments each. If
fewer than 6 arguments are passed to either, the additional ifelse() macros evaluate to nothing and don't interfere with the
arithmetic. Hence a complicated SQL expression like
t.A <= 4 and (t.B = 6 or t.C = s.D) and t.E > 3
might be more easily understood (by some) as
_and(_len(t.A,4), _or(_eqn(t.B,6),_eqn(t.C,s.D)), _gtn(t.E,3)) = 1
pletch@mcs.newpaltz.edu
