next »

[.]

The 2009 version looks as follows:

Code

FUNCTION nvl(l_original, l_if_null)
 
DEFINE
   l_original   STRING,
   l_if_null    STRING
 
DEFINE
   l_result   STRING
 
   IF l_original IS NULL
   OR LENGTH(l_original) == 0 THEN
       LET l_result = l_if_null
   ELSE
      LET l_result = l_original
   END IF
 
   RETURN l_result
 
END FUNCTION

[Sebastien F.]

Reuben,

I used to have a nvl() and if() in our libraries in my previous job.

The 1996 version of our nvl is available in the IIUG software repository

FUNCTION nvl(l_original, l_if_null)
DEFINE  l_original                     CHAR(80),
        l_if_null                      CHAR(80)

    IF LENGTH(l_original) = 0 THEN
        RETURN l_if_null
    ELSE
        IF l_original[1]="$" THEN   # Cope with money fields
           LET l_original[1]=" "
        END IF
        RETURN l_original
    END IF

END FUNCTION

Yes I expected that people can write such a utility function....

However if we would implement that as a real operator, I believe we could keep the original type of the value, and that's important for example when formatting values for output:

Try this:

Code

MAIN
    DEFINE x1, x2 DECIMAL(10,2)
    LET x1 = 1234.56
    LET x2 = NULL
    DISPLAY "Value x1         : [", x1, "]"
    DISPLAY "Value x2         : [", x2, "]"
    DISPLAY "Value x1 MOD 2   : [", x1 MOD 2, "]"
    DISPLAY "Value x2 MOD 2   : [", x2 MOD 2, "]"
    DISPLAY "Value NVL(x1,0)  : [", NVL(x1,0), "]"
    DISPLAY "Value NVL(x2,0)  : [", NVL(x2,0), "]"
END MAIN

You get:

Code

Value x1         : [     1234.56]
Value x2         : [            ]
Value x1 MOD 2   : [          0]
Value x2 MOD 2   : [      ]
Value NVL(x1,0)  : [1234.56]
Value NVL(x2,0)  : [0]

Seb

[Andrew C.]

Sorry to come so late to the thread, but if I may play devil's advocate for a moment before I get serious?

If you add a IFF(cond, expr1, expr2) type of operator, can it be defined so that

Code

let expr = null
let x = iff(expr, 10, 20)

yields null? it's only consistent, and I think more correct. If people want to avoid that, then of course the suggested NVL function will help them:

Code

iff(nvl(expr, false), 10, 20)

So rewriting Reuben's IFF function, I would have written:

Code

function IFF(cond, x, y)
    case cond
      when cond != false return x
      when cond == false return y
      otherwise return null
    end case
end function

(I am using tests against false (aka zero) just so that cond can actually be any non-zero number for truth)

Also the point about both x and y being evaluated before the call is definitely a problem so I'm not a fan of this type of work-around function.

What's more curious to me is the apparent fear of NULL that is so common both in my company and around the world. I agree, the null-behaviour of || is kind-of annoying, but technically it's more correct, and I must confess that the annoyance factor is only because I'm so used to the polite but technically inappropriate behaviour of the comma concatenate operator.

I don't understand the need to "code around" the so-called null problem. I find that null's behaviour is great for REDUCING code. There are a few simple tricks that help:

Code

if a >= b or a is null or b is null then DO_WORK end if

becomes

Code

if a < b then else DO_WORK end if

This is a trivial trick for inverting the flow and reducing code when you need it. The sample CASE statement in my function above is also often surprising to many people but it's a great technique. A built-in NVL would be fantastic because it could return the real object with it's original data type instead of all being converted to a string. A built-in IFF() would be nice - how's about allowing more pairs too?

Code

iff(c1, r1, c2, r2, c3, r3, e1)

for an elif effect.

There is one critical point I would like to make about the choice of operator: I do not like the suggestion that the keyword IF be used as a function. We are writing a lot of code-metric scripts, so having to cope with a STATEMENT keyword as a possible operator would play havoc with the parsers. I know that 4GL and SQL are not supposed to have keywords as such, but seriously, I'm sure we're not the only company on the planet writing ad-hoc parsers for 4GL. PLEASE do not use the IF keyword!

Why not use the world-standard ternary operator ?: from C? Neither ? nor : are important tokens in 4GL

Code

let x = cond ? a : b
call ftn(cond ? a : b, c, d)

looks fine to me. If you choose to go this way, could I also request that you resist the temptation to make it ass-backwards like Python's inline     a if cond else b     rubbish? There's merit in copying the style of the familiar rather than trying to be too clever...

Finally, a built-in concat() operator would be very nice and would increase overall comfort.

Just my 2 pfennigs worth.

[Andrew C.]

I had a failure to new-paragraph in my previous post:

This is a trivial trick for inverting the flow and reducing code when you need it. The sample CASE statement in my function above is also often surprising to many people but it's a great technique. A built-in NVL would be fantastic because it could return the real object with it's original data type instead of all being converted to a string. A built-in IFF() would be nice - how's about allowing more pairs too?

should be

This is a trivial trick for inverting the flow and reducing code when you need it. The sample CASE statement in my function above is also often surprising to many people but it's a great technique.
[NEW PARAGRAPH]
A built-in NVL would be fantastic because it could return the real object with it's original data type instead of all being converted to a string. A built-in IFF() would be nice - how's about allowing more pairs too?

[Reuben B.]

I don't understand the need to "code around" the so-called null problem. I find that null's behaviour is great for REDUCING code. There are a few simple tricks that help:

Code

if a >= b or a is null or b is null then DO_WORK end if

becomes

Code

if a < b then else DO_WORK end if

This is a trivial trick for inverting the flow and reducing code when you need it.

Agreed.  If I want to check that a is less than b, the temptation is to test only for the error condition...

Code

IF a>=b THEN
   ERROR "A must be less than B"
   NEXT FIELD CURRENT
END IF

which if a or b is NULL will have an unexpected result.

Better to test for the good condition

Code

IF a<b THEN
   # Ok Do nothing
ELSE
   ERROR "A must be less than B"
   NEXT FIELD CURRENT
END IF

and if a or b is NULL then the test will fail as well. 

If you add a IFF(cond, expr1, expr2) type of operator, can it be defined so that


Code: (genero)
let expr = null
let x = iff(expr, 10, 20)
yields null? it's only consistent, and I think more correct. If people want to avoid that, then of course the suggested NVL function will help them:

...or maybe consider in addition to  IFF(expr,10,20)

IFFN(expr,10,20,NULL) -- returns last argument if expr IS NULL

... and keep everyone happy.

Also the point about both x and y being evaluated before the call is definitely a problem so I'm not a fan of this type of work-around function.

I have often wondered if we should have a means to specify a lazy AND, and a lazy OR so that ...

IF A AND B
IF A OR B

... B is only evaluated where necessary.  It also helps with coding the else expression once rather than twice e.g.

Code

IF A lazy-and B THEN
   # OK
ELSE
   C
END IF
 
versus 
 
IF A THEN
   IF B THEN
      #OK
   ELSE
      C
   END IF
ELSE
   C
END IF

Why not use the world-standard ternary operator ?: from C? Neither ? nor : are important tokens in 4GL

Genero Report Writer uses the ?: notation. e.g. to display negative numbers in a red font ...

field.value<0?Color.RED:Color.BLACK

I wasn't a big fan of this as it is not consistent with our existing 4GL, and to the untrained eye it is not readable. 
Given the choice between

Code

IF expr THEN
   A
ELSE 
   B
END IF
 
IF(expr,A,B)
 
expr?A:B

I'd prefer to code IF(expr,A,B) in both products as it is both readable and precise.

Reuben

[Andrew C.]

Agreed.  If I want to check that a is less than b, the temptation is to test only for the error condition...

Code

IF a>=b THEN
   ERROR "A must be less than B"
   NEXT FIELD CURRENT
END IF

which if a or b is NULL will have an unexpected result.

Better to test for the good condition

Code

IF a<b THEN
   # Ok Do nothing
ELSE
   ERROR "A must be less than B"
   NEXT FIELD CURRENT
END IF

and if a or b is NULL then the test will fail as well. 

Ummm, but if a or b is null, then you CAN'T say a has a bad value in relation to b. You don't know. First you need to tell the user that a and b must be filled in, and that's a separate test and separate error message if you care about clarity. Or, if the required fields are only tested at ACCEPT (as our code does), then the first version is better because it politely keeps quiet about fields the user hasn't filled in yet. However! that's an argument over UI style, and either case shows that choosing which way to handle null can be seamless rather than a chore. QED.