Page 1 of 1


PostPosted: 19 Feb 2013, 21:33
by Ursego
The function IfNull(<checked value>, <alternative value>) returns the checked value if it is NOT NULL; otherwise, it returns the alternative value.

It mimics the nvl() function of Oracle. In Transact-SQL language such a function has the name IsNull() (which is pretty confusing since it DOESN'T determine if something is null). Some languages (Kotlin, Gosu) use ?: (the Elvis operator - if you tilt your head to left, the operator becomes the haircut and the eyes of Elvis :o ). C# has ?? (the null-coalescing operator). Many languages don't have that functionality at all, so developers are forced to check the value for null manually, like in this example for Java:

Code: Select all
String var = value != null ? value : alternative

PowerScript is in that club, but nothing in the world stops us from creating a global function like the one proposed here.

The function IfNull() 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 must have a same datatype.

Examples of use:

Code: Select all
ll_row_count = IfNull(uf_get_row_count(), 0)
li_min_allowed_age = IfNull(uf_get_min_allowed_age(), 18)
ls_full_name = ls_first_name + " " + IfNull(ls_mid_name + " ", "") + ls_last_name // ls_mid_name is optional
ls_err = "Invalid as_mode " + IfNull("'" + as_mode + "'", "NULL") + "." // prevent NULLifying of ls_err
ls_err = "Invalid ai_mode '" + IfNull(String(ai_mode), "NULL") + "'." // both the arguments must have a same datatype!
lb_value_changed = (IfNull(ls_new_value, '') <> IfNull(ls_old_value, ''))
lcb_clicked_button = IfNull(acb_clicked_button, cb_cancel)


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.

Re: nvl()

PostPosted: 27 Apr 2015, 21:06
by benconsult
Of course you could just use the any variable type for IfNull, and not have to worry about overloading:

Code: Select all
global function any IfNull (any aa_checked_value, any aa_alternative_value);

if IsNull(aa_checked_value) then
   return aa_checked_value
   return aa_alternative_value
end if
end function

Re: nvl()

PostPosted: 12 May 2015, 14:37
by Ursego
Unfortunately, it's not a good way - it allows to pass arguments of different datatypes with no compilation time error, i.e. it's not type safe:

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

In fact, the first version was with "any" arguments, but later I switched to the overloaded version.

Re: nvl()

PostPosted: 13 May 2015, 18:16
by benconsult
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 IfNull, 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.

Re: nvl()

PostPosted: 24 May 2015, 07:59
by Ursego
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:

benconsult wrote:you can always make a function that's using an Any argument more robust by checking the classname to avoid runtime errors

Why to impact performance? Why to do easy things in a difficult way?