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.
The only system setting that will change this is ZERO which I cannot
change.
To make matters worse, if I retrieve a NULL REAL column on a form into
a variable, the variable is populated with zero, not null.
If I test the column in the form variable using IFNULL I get identical
results regardless of whether the value is zero or null.
Best Regards,
Dennis McGrath
> Very Best R:egards,
>
> Razzak.
>
>
> At 01:10 PM 10/7/2003 -0700, you wrote:
>
> >Sorry Razzak,
> >
> >No help there. The expanded demo shows that nothing I can think of
> will
> >solve my problem. To make matters worse, if I retrieve the column
> into
> >a variable, the value retrieved is zero not NULL. I usually can
> figure
> >a workaround but this is a real stumper. Even CTXT retrieves zero as
> >demoed when you run this code. I can send you a demo db with a
> simple
> >form to show the problem, if you need it.
> >
> >set eqnull on
> >set null -0-
> >set zero on -- required
> >
> >set var vreal real = null
> >set var vtest = (ifnull(.vreal,'is null','is not null'))
> >wri .vreal, .vtest
> >set var vtest = (ifgt(.vreal,0, 'gt 0','not gt 0'))
> >wri .vreal, .vtest
> >set var vtest = (ifeq(.vreal,0, 'eq 0','not eq 0'))
> >wri .vreal, .vtest
> >set var vtest = (iflt(.vreal,0, 'lt 0','not lt 0'))
> >wri .vreal, .vtest
> >set var vtest = (ifexists(.vreal,'exists','not exists'))
> >wri .vreal, .vtest
> >set var vtest = ('x' & CTXT(.vreal) & 'x')
> >wri .vreal, .vtest
> >
> >
> >wri ' '
> >set var vreal real = 0
> >set var vtest = (ifnull(.vreal,'is null','is not null'))
> >wri .vreal, .vtest
> >set var vtest = (ifgt(.vreal,0, 'gt 0','not gt 0'))
> >wri .vreal, .vtest
> >set var vtest = (ifeq(.vreal,0, 'eq 0','not eq 0'))
> >wri .vreal, .vtest
> >set var vtest = (iflt(.vreal,0, 'lt 0','not lt 0'))
> >wri .vreal, .vtest
> >set var vtest = (ifexists(.vreal,'exists','not exists'))
> >wri .vreal, .vtest
> >set var vtest = ('x' & CTXT(.vreal) & 'x')
> >wri .vreal, .vtest
> >
> >Best Regards,
> >Dennis McGrath
> >
> >--- "A. Razzak Memon" <[EMAIL PROTECTED]> wrote:
> > >
> > > Dennis,
> > >
> > > What's the settings for EQNULL ?
> > >
> > > Correct evaluation of expressions when EQNULL is SET to ON.
> > >
> > > R:BASE 6.5++ (Build:1.866xRT03 and higher)
> > >
> > > R:BASE uses special processing on simple IF commands for maximum
> > > speed. However, the way these were processed was not consistent
> > > with the way complex IF commands or WHILE commands were processed
> > > with regard to comparisons using NULL values. The EQNULL setting
> > > at TRUE means that a comparison between two NULL values is a
> match
> > > and that a comparison between a NULL value and a non-NULL value
> is
> > > not. When EQNULL is set to FALSE then a comparison between two
> NULL
> > > values is not a match nor is a comparison between a NULL value
> and
> > > a non-NULL value a mismatch. The NULL value essentially make the
> > > whole thing "unknown'. This behavior was followed in WHILE and
> > > complex IF commands but was not followed in simple IF commands. A
> > > simple IF command has only one comparison and the left side is a
> > > simple variable and the right side is either a variable or a
> > > constant. A simple IF does not have any expressions.
> > >
> > > Compare these code samples:
> > >
> > > SET VAR v1 TEXT = NULL
> > > SET VAR v2 TEXT = NULL
> > >
> > > SET EQNULL OFF
> > > IF v1 = .v2 THEN
> > > -- will not be a hit
> > > ENDIF
> > > IF v1 <> .v2 THEN
> > > -- will not be a hit
> > > ENDIF
> > > IF v1 <> 'This' THEN
> > > -- will not be a hit (it used to be before this fix)
> > > ENDIF
> > >
> > > SET EQNULL ON
> > > IF v1 = .v2 THEN
> > > -- will be a hit
> > > ENDIF
> > > IF v1 <> .v2 THEN
> > > -- will not be a hit
> > > ENDIF
> > > IF v1 <> 'This' THEN
> > > -- will be a hit
> > > ENDIF
> > >
> > > Before this fix, the comparison "IF v1 <> 'This' THEN" would be
> > > a hit with EQNULL set ON or FALSE when it should only be a hit
> > > when EQNULL is ON. This means that now:
> > >
> > > "IF (.v1) <> 'This' THEN" and "IF v1 <> 'This' THEN"
> > >
> > > will both process the same way. In the past they would be
> > > different because of this problem.
> > >
> > > In your code if you want the comparison of a NULL variable and
> > > a non-NULL constant to be a hit then you should run with EQNULL
> > > set ON.
> > >
> > > In your code if you want the comparison of a NULL variable and
> > > a non-NULL constant to be a hit then you should run with EQNULL
> > > set ON.
> > >
> > > Let me know how that goes ...
> > >
> > > Hope that helps!
> > >
> > > Very Best R:egards,
> > >
> > > Razzak.
> > >
> > > At 12:42 PM 10/7/2003 -0700, Dennis McGrath wrote:
> > >
> > > >I've got a problem has me stumped. I have a client where SET
> ZERO
> > > is
> > > >always ON. I won't go into the details, but this is a must.
> > > >
> > > >Running the latest patch of 6.5++ both DOS and Win.
> > > >
> > > >I need to test a column (REAL) on several entry forms to see if
> it
> > > is
> > > >NULL or zero. Seems the ISNULL function is not up to the task.
> Here
> > > is
> > > >some demo code:
> > > >
> > > >set null -0-
> > > >set zero on -- required
> > > >set var vreal real = null
> > > >set var vtest = (ifnull(.vreal,'is null','is not null'))
> > > >wri .vreal, .vtest
> > > >wri ' '
> > > >set var vreal real = 0
> > > >set var vtest = (ifnull(.vreal,'is null','is not null'))
> > > >wri .vreal, .vtest
> > > >
> > > >
> > > >vTest always = 'is null'
> > > >
> > > >Anyone have a magic bullet that will solve my problem?
>
