At 11:06 AM 10/16/2001 +0100, you wrote:
>Many thanks for everyone's replies.
>
>A number of points have been raised which I'll try to address:
>
>The settings of ZERO and EQNULL had no effect in any combination of off or
>on - except that I got different error message - but I always got a zero in
>the result and never a null.
>
>Bernie, you said it worked for you so could you please show me the syntax
>you used for the second argument. I've tried both -0- and null both enclosed
>in quotes and not.
I stand corrected. I had not tested all possible conditions of
everything when I wrote my response to you. Now I have. Here are the
results and I might add this has now been reported to RBTI as a bug.
I used the word NULL not enclosed in quotes. Since this is an
R:Base keyword one does not enclose it in quotes. My syntax was identical
to yours.
SET VAR vx DATE
vx = (IFNULL(i_date, NULL, (i_date +2))) IN Invoices WHERE invoice = '5923'
This expression works perfectly with i_date being either a valid
date or NULL as long as EQNULL is set to ON and ZERO is ON but not OFF.
When EQNULL is ON and ZERO is OFF I get "Insufficient buffer space
to process expressions. (2136)" if i_date is null. This is followed by a
row not found error, 2059, even though the row is in the table. When both
EQNULL and ZERO are ON there is no error message. Hmmmmm, so let's try that
with EQNULL OFF. Same result. It appears that the ZERO setting is
interacting with the IFNULL function to produce an error condition when ZERO
is set to OFF. See the table below.
We wind up with the following table of outcomes:
EQNULL ZERO i_date Resulting Condition
------------------------------------------------
ON ON NOT NULL OK
ON ON NULL OK
ON OFF NOT NULL OK
ON OFF NULL Error 2136
Warning 2059
OFF ON NOT NULL OK
OFF ON NULL OK
OFF OFF NOT NULL OK
OFF OFF NULL Error 2136
Warning 2059
I tested this with only a DATE column.
Bernie
