To all who are interested:
Well, it was not obvious, but I did some more testing and fell on a
solution to testing a column on a form to see if it is null when ZERO
ON setting is in effect.
Turns out that a form works exactly as if the data is retrieved with a
select, even in enter mode. SELECTing null number data into a variable
with ZERO ON retrieves zero. But SELECTING the result of IFNULL
returns a unique value dependent on the null state of the column.
Turns out the following works for me in a form:
vTestReal INT = (IFNULL(colname,1,0))
VTestReal = 1 if the column is null and 0 if the column is not.
This works just like a select from the table at the R> prompt. It
appears to be the one and only way to discover a null number in the
form when ZERO ON is in effect. Works for me.
Dennis McGrath
--- Dennis McGrath <[EMAIL PROTECTED]> 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.
>
> 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
>
=== message truncated ===
