10 replies to this topic

[Rory]

Hello

A test for zero is sometimes unreliable e.g.
'IF #X = 0' does not work when #X actually is zero.
I have come across this over the years and just worked around it by saying 'IF #X = #ZERO' while never giving #ZERO a value.
I have only had this problem with an Oracle database.
I have also seen it when initialising a variable e.g.
'#X = 0' results in #X being given a seemingly random value.
It seems to happen in larger functions.
Just wondered if there is more to it?

Thanks
Rory

[Rory]

Muchos gracios Juan!

[Rory]

Thanks Rajesh

[Chris Mackenzie]

Since v1.4 I've relied on scratch numerics
being initialised to zero at function entry
and have never experienced a problem. I can
see the problem with the literal being changed,
but otherwise - not. The issue in C is not a
problem - that's normal behaviour in any language
for an undefined value.

As to only happens with Oracle - what does that
have to do with scratch vars? In Oracle PL/SQL
there _is_ a case where neither condition of an
IF might be executed,

e.g.
let x be null and Y = 1

if x = y then
do this
else
do that
endif

Neither this or that is done unless you do it like this

if nvl(x,0) = y then
do this
else
do that
endif

so you provide a default value for the case
where the var is null to avoid an undefined
result; but I fail to see how this could
impact P4 code unless we are talking about
file vars and in any case numerics (for P4
at least) should always be defined with
a default of zero in Oracle tables to avoid
the null issue.

Show me a real example that doesn't work because
I need convincing!

[Richard Bassett]

Just like to second all that Chris M. says.

I don't think any such problem exists in ProIV and if you can reproduce it I'd be very interested to see it.

Also, as Chris indicates, 'auto' (as opposed to static) variables declared in C are SUPPOSED to contain junk if you don't initialise them (the language specifies it).

ProIV variables are analogous to 'static' C variables.

Unclear what you mean about 0 displaying as blank too, CONV does that always so you can't mean that right?

Personally, I always try to ban SQL NULL values from ProIV applications because they just cause chaos


Richard.

[Chris Mackenzie]

And another thing...

Even if you select a numeric column from an
oratable with a null value, P4 will see it
as a zero - and if you assign it to a scratch
P4 still sees it as zero...

[lastenia_rojas]

A clarification regarding Oracle handling of NULLS:

In PL/SQL the else part of an IF statement is indeed executed when the IF condition includes variables with NULL values and the condition evaluates to 'unknown'. In other words, in the following piece of PL/SQL:

DECLARE
   X VARCHAR2(1);
   Y VARCHAR2(1);
BEGIN
   X := NULL;
   Y := 'A';

   IF X = Y THEN
      ...
   ELSE
      ...
   END IF;
END;

the code under the ELSE **will** execute.

This does not mean that the condition in the IF statement is false. It just means that it's unknown.

From the Oracle documentation:
'A condition that evaluaates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN'.

[Chris Mackenzie]

Well ok, that was the wrong example,
in the wrong forum too...

what about

DECLARE
X VARCHAR2(1);
Y VARCHAR2(1);
BEGIN
X := NULL;
Y := 'A';

IF X != Y THEN
return 'true';
ELSE
return 'false';
END IF;

That will return 'false' - good eh?

as will:

DECLARE
X VARCHAR2(1);
Y VARCHAR2(1);
BEGIN
X := NULL;
Y := NULL;

IF X = Y THEN
return 'true';
ELSE
return 'false';
END IF;