CALC-SET-IF with SQL Null

Many views of data construe data to have one of two possible states: either TRUE or FALSE. A broader view construes data to have one of three possible states: TRUE, FALSE and Undefined. The later state in called null or SQL-null within the SQL language.

Within TB API, any variable that is defined with SET or CALC is assigned the SQL-null value if any of the terms of the defining expression is SQL-null.
For example, for:
     Field1 = 'Test'
     Field2 = (null)
     Field3 = 'Data'
           SET Value1 = Field1 ! Field2 ! Field3
      results in (null)

If the /N option is included with the SET or CALC command, this rule is reversed. That is, any variable that is defined with SET or CALC is not assigned the SQL-null value if any of the terms of the defining expression is SQL-null.
For the previous example:
           SET/N Value1 = Field1 ! Field2 ! Field3
      results in: 'Test     Data'

An analogous usage of the /N option exists for the CALC statement.

The SET/N option works for global variables, local variables and data base columns. Note however, in the above example, that if global variables had been used, the result would have been slightly different. In this case:
           SET/N %Value1 = %Field1 ! %Field2 ! %Field3
      results in: 'Test Data'

that is, the number of intervening blanks occupied by the overridden SQL-null item is not constant.

Use of SET/N with the distinct() function()

The application of the /N option on SET and CALC has limited use. One notable use of SET/N is to create the parameter for the distinct() function.