nvl()


Link to this posting

Postby Ursego » 19 Feb 2013, 21:33

The function nvl(<1st value>, <2nd value>) returns the 1st value if that value is NOT NULL; otherwise, it returns the 2nd value.

In contrast to iif(), which was stolen from VB, nvl() is stolen from Oracle :lol: .

Examples of use:

Code: Select all
ll_elephants_count = nvl(uf_get_elephants_count(), 0) // in case uf_get_elephants_count() can return null
ls_full_name = ls_first_name + " " + nvl(ls_mid_name + " ", "") + ls_last_name // ls_mid_name is optional
ls_err = "Argument as_mode contains invalid value " + nvl("'" + as_mode + "'", "NULL") + "." // prevent NULLifying of ls_err

The function is overloaded for the following datatypes: string, long, double, boolean, PowerObject (overloading of global functions is described here). Of course, both the arguments and the returned value have a same datatype. In the next example, the integer variable ai_mode is cast to string:

Code: Select all
ls_err = "Argument ai_mode contains invalid value '" + nvl(String(ai_mode), "NULL") + "'."


HOW TO ADD THE FUNCTION TO THE APPLICATION

1. Save the file spy.pbl on your hard disk (in the folder where the PBLs are stored). The function is in that PBL.
2. Add it to your application's library list.
User avatar
Ursego
Site Admin
 
Posts: 124
Joined: 19 Feb 2013, 20:33

Link to this posting

Postby benconsult » 27 Apr 2015, 21:06

Of course you could just use the any variable type for nvl, and not have to worry about overloading:

Code: Select all
global function any nvl (any aa_variable, any aa_otherwise);

if isnull(aa_variable) then
   return aa_otherwise
else
   return aa_variable
end if
end function
benconsult
 
Posts: 2
Joined: 27 Apr 2015, 20:59

Link to this posting

Postby Ursego » 12 May 2015, 14:37

Unfortunately, it's not a good way - it allows to supply arguments of different datatypes with no compilation time error, i.e. it's not a type safe solution:

Code: Select all
ls_err = "Argument ai_mode contains invalid value '" + nvl(ai_mode, "NULL") + "'." // runtime error - concatenating string with int!

In fact, the first version was with "any" arguments, but later I switch to the overloaded version.
User avatar
Ursego
Site Admin
 
Posts: 124
Joined: 19 Feb 2013, 20:33

Link to this posting

Postby benconsult » 13 May 2015, 18:16

It's no more dangerous than using dot notation to load a value from a datawindow into a variable without typechecking. If fact, it's probably less so, since most of the time using nvl, you would supply a literal, such as an empty string, or a zero, and so type mismatches would be obvious.

However, you can always make a function that's using an Any argument more robust by checking the classname to avoid runtime errors.
benconsult
 
Posts: 2
Joined: 27 Apr 2015, 20:59

Link to this posting

Postby Ursego » 24 May 2015, 07:59

benconsult wrote:It's no more dangerous than using dot notation

But it's also not less dangerous! Dot notation is not type-safe, and it IS a problem. But it's not only a matter of type safety. In addition, each solution with ANY datatype involves two extra castings - from the original type to ANY and back. Shortly - it is not elegant. :lol:
User avatar
Ursego
Site Admin
 
Posts: 124
Joined: 19 Feb 2013, 20:33




IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:




cron
free counters

eXTReMe Tracker