Hooray Dennis, I knew you'd find a way!
David Blocker
----- Original Message -----
From: "Dennis McGrath" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
Sent: Saturday, October 11, 2003 10:43 AM
Subject: [RBASE-L] - Problems with ZERO ON - Solution!
> 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 ===
>
