Assignment, Conditions, and Expressions with Fields allowing SQL Null

This section described the behavior of SQL Null fields in

The following field definitions are used throughout the text:

Define Field(#A) Type(*DEC) Length(9) Decimals(0) Input_Atr(ASQN) Default(*SQLNULL)
Define Field(#B) Type(*DEC) Length(9) Decimals(0) Input_Atr(ASQN) Default(*SQLNULL)

Define Field(#C) Type(*DEC) Length(9) Decimals(0) Default(*NULL)

Also See

ASQN (Allow SQL Nulls) attribute

Specifying Conditions and Expressions in the Technical Reference Guide.

Assignment

If you wish to ensure that SQL Null fields are handled via ANSI rules for assignment, enable the *STRICT_NULL_ASSIGN function option. This option causes a fatal error to occur at execution time if the source field is SQL Null and the target field does not have the ASQN attribute.

A field allowing SQL Null may be explicitly set to SQL Null, as in the following example.

#B := *SQLNULL

 

A field that is currently SQL Null may be assigned to another field. If the target field allows SQL Null, it will be set to SQL Null. In the following example, #A becomes SQL Null because #B was SQL Null.

#A := #B

 

If the target field does not have the ASQN attribute, the behavior varies depending on whether the *STRICT_NULL_ASSIGN function option is enabled. By default, the *NULL value for the field type will be assigned to the target field. In the example below, as #C is a numeric field, it would be set  to zero. For a definition of what the *NULL value is for each of the field types refer to CHANGE Parameters.

#C := #B

 

However, if *STRICT_NULL_ASSIGN has been enabled, and the example code above is executed when #B is SQL Null, a fatal error will occur as the target field does not support being set to SQL Null. When working with *STRICT_NULL_ASSIGN, the LANSA Developer must code carefully to protect against such runtime errors. For example:

If (*Not #B.IsSqlNull)

#C := #B

Else

Message Msgtxt('#B is SQL Null')

Endif

 

You can also use the .AsValue intrinsic method to treat an SQL Null field as a different value. This is useful for mathematics and concatenation, where SQL Null or *NULL are not appropriate values. In the following example, we now get the result of 5 in #C if #B is SQL Null. However, if #B was 3, #C would be set to 15 because #B.AsValue only affects #B when it is SQL Null.

#C := #B.AsValue( 1 ) * 5

 

Also See

*SQLNULL Keyword

Intrinsic Property .IsSqlNull

Intrinsic Method .AsValue

Conditions

IF COND((#DATE2.IsSqlNull) *orif (#DATE1 *gt #DATE2))

#DATE2 := #DATE1

ENDIF

 

    (#A.IsSqlNull) *orif (#A.IsNull)

 

#A.AsValue(1) *EQ 1

 

The following table summarizes the result of various conditions, with the sample fields #A and #B both SQL Null, and #C *ZERO.

Condition

Result

#A.IsSqlNull

TRUE

#A.IsNull

FALSE

(#A.IsSqlNull) *orif (#A.IsNull)

TRUE

IF_NULL(#A #B #C)

FALSE

IF_NULL(#C)

TRUE

#A.AsValue(*ZERO) *EQ *ZERO

TRUE

#A *EQ *ZERO

FALSE

#A *EQ #B

FALSE

#A *LE #B

FALSE

#B *EQ #C

FALSE

#B *LE #C

FALSE

 

 

Expressions

When expressions are being evaluated, intermediate results retain the SQL Null state. They are ALWAYS strictly interpreted. For example, when #B is SQL Null,  the result of expression '#B + 1' is SQL Null. That is, SQL Null plus 1 is still SQL Null. This is independent of the attributes of any result field.

It is only when the result of the expression is assigned into the result field that a difference in behaviour can occur. If the result of an expression is SQL Null, behaviour depends on whether the result field allows SQL Null and also on the function option *STRICT_NULL_ASSIGN. Refer to Assignment for details.

If you wish to change the value of SQL Null fields to something more appropriate, use Intrinsic Method .AsValue. For example, the result of expression '#B.AsValue(1) + 1' is 2 when #B is SQL Null (and 5 when #B is 4).

Ý SQL Null Handling