Foxpro NVL() behavior

[quantzombie]

Hi All,

If I use nvl(var1,foo(xyz)) will the function foo executed everytime even if var is not NULL ?

 

[Olaf Doschke]

Well,
you can test this yourself.
For example Nvl(value,MessageBox("test")) does show the message box.

As alternative you could use IIF(ISNULL(value),MessageBox("test"),value).
This will only evaluate the 2nd or 3rd parameter, not both.

NVL is good for simpler cases with a default value as 0, "", DATE() or anything of that kind, if foo(xyz) does not involve interaction and is fast, nothing speaks against that, too, it's still the shorter notation.

Bye, Olaf.

 

[Mike Lewis]

The answer to your question is Yes. A simple test will demonstrate that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[quantzombie]

Thanks guys. I was looking into an old code and couldn't believe even after i debugged it out
The function called is very complex and takes considerable time to execute. Probably will save around an hour after i fix it as its being called in a SCAN LOOP

 

[Mike Lewis]

It might be easier to use ISNULL() to determine if the variable is NULL, and only call the function if it is:

IF ISNULL(var1)
  Foo(xyz)
ENDIF

Also, of the function really is taking that long to execute, maybe you need to look for ways to optimise it.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Better still, if you use IIF() in place of NVL(), the function won't execute if the variable is NULL:

IIF(ISNULL(var1), Foo(xyz), var1)

That looks like the easiest solution.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike, exactly what I proposed already.

the function won't execute if the variable is NULL:

That should be: The functiion only executes, if the variable is NULL. But the expression is right for replacing NVL(var1,foo(xyz))

Bye, Olaf.

 

[Mike Lewis]

That should be: The functiion only executes, if the variable is NULL.

You're right, Olaf (of course).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi guys,

I think your mixing things up


Function NVL will be executed asa it's called
Function foo(xyz) will ONLY be executed if var1 = .NULL.

hth

MarK

 

[Mike Lewis]

Hi Mark,

No, we weren't mixing things up. The question - and our answers - all clearly relate to the Foo() function. There's no question that NVL() will always be called.

Function foo(xyz) will ONLY be executed if var1 = .NULL.

Not according to the following test:

x = NULL
? NVL(x, Foo(x))

FUNCTION Foo
LPARAMETERS tlX
MESSAGEBOX("Hello World")
RETURN 2

Set x to NULL, and the messagebox displays; the code displays 2.

Set x to any number, and the messagebox displays; the code displays whatever you set x to.

So, NVL() returns the expected result. But, either way, the Foo function is executed.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mike

x = NULL is not the same as x = .NULL. which is the right notation for "nulling" a variable

hth

MarK

 

[Olaf Doschke]

You can use both .NULL. and NULL nowadays, as you can also use AND and .AND. or and .OR., test for yourself.

Mike already explained very well your other misconception. NVL truly does evaluate the second expression, too, even if not needed. Only IIF() first checks which expression to evaluate.

To make the point clear, I said

For example Nvl(value,MessageBox("test")) does show the message box.

and I could have made this more clear by adding: ...no matter what the value is.

That means the messagebox always shows with NVL, and if you don't like that use IIF.

Bye, Olaf.

 

[mjcmkrsr]

Hi Mike,

Maybe I'm mixing things up

---
From Visual FoxPro Hacker's Guide

uNonNullValue = NVL(uTestExpr, uSubstituteValue)

NVL() provides a way to keep nulls from propagating without having to use IsNull(). uTestExpr is tested. If it's not null, its value is returned. If uTestExpr is null, the value of uSubstituteValue is returned

---

According to my understanding of the above NVL(var1,foo(xyz)) calls FOO(xyz) ONLY if VAR1 = .NULL.

On the other hand if NVL(var1, foo(xyz)) ALWAYS calls foo(xyz) why then test it?

MarK

 

[Olaf Doschke]

You still don't get it fully. While foo() always is called, the return value of NVL still can be var1, if var1 is not null.

This is just a flaw of the NVL() implementation: It always evaluates the alternative expression, even if it isn't needed, but it does return what is documented in the help you quote.

Bye, Olaf.

 

[mjcmkrsr]

Hi Olaf,


Thanks for this explanation. Helps a lot

MarK

 

[Mike Lewis]

Mark, if you're still not sure about this, why don't you test it for yourself? It only takes a couple of minutes to construct a simple test. You can either do the sort of code that I showed, or you can omit the messagebox and run the whole thing in the Trace window.

Also, as Olaf explained, x = NULL and x = .NULL. are identical. Again, if you run your own test, you can try both formats; you'll see that you get the same results in both cases.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Sorry, I meant from the hacker's guide, not from help.

One more way to think of this: If you would implement NVL as a user defined function you wouldn't be able to get around this quirk:

Function myNVL(tTestForNull, tAlternativeValue)
   If Isnull(tTestForNull)
      Return tAlternativeValue
   Else
      Return tTestForNull
   Endif
Endfunc

Now, if you call that user defined function with MyNVL(var1,foo(xyz)) of course foo(xyz) also is executed, even before the first line of myNVL runs, as the result value of the foo(xyz) call is what is needed first to be able to pass it in to myNVL.

Test yourself again: Call ? myNVL(.NULL.,messagebox("hello")), then call ? myNVL("world",messagebox("hello")), both times you get the messagebox, but once you get 1 (the messagebox return value for the OK button) and once you get "world" printed. And NVL unfortunately behaves the same way.

So the way IIF works really is special, the way NVL behaves is quite normal for a function getting parameters passed in. But indeed the VFP Team could have done NVL in the same manner as IIF or also in the same manner, in which they shorten the evaluation of logical expressions, once the total outcome is inevitable.

Bye, Olaf.

 

[mjcmkrsr]

Hi Mike & Olaf,

I believe your tests (I'll try them this evening).

What struck me is the (bad) explanation of NVL()

---
NVL() provides a way to keep nulls from propagating without having to use IsNull(). uTestExpr is tested. If it's not null, its value is returned. If uTestExpr is null, the value of uSubstituteValue is returned.
---

I didn't find anywhere a hint that the second expression would also be evaluated.

Thx

MarK

 

[Olaf Doschke]

Indeed the help topic text on IIF is very verbose about when which expression is evaluated. But RETURNING an expression is one thing, evaluation another. Both are evaluted, even though only one is needed anyway.

What do you expect from MAX(val1,val2)? To only evaluate val2, if val1 is smaller? ;o) Just joking, of course here you need both values to compare them.

More general, what do you expect from function(x,y)? Do you expect, that y is only passed in at some condition? If a function has two parameters it has two parameters, you can have optional parameters, but the way to not pass them in is to skip them in the call, eg by calling function(x) only. But NVL needs both parameters, none is optional, the same way IIF needs three parameters always.

Don't look at it from the perspective of the explanation, it's merely a function working like any other function. It's just the special meaning making you think it should behave different. The real "miracle" is how IIF was done, not how NVL was not done.

Bye, Olaf.

 

[Mike Lewis]

The real "miracle" is how IIF was done, not how NVL was not done

True.

But IIF() is consistent with the following:

x = Func1() OR Func2()

If Func1() returns .T., then VFP refrains from evaluating Func2() - because it won't affect the outcome.

Similarly:

x = Func1() AND Func2()

If Func1() is .F., Func2 is not evaluated.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads