Page History
...
| Code Block |
|---|
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.
...
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
- To test for SQL Null, you should use *IS *SQLNULL or *ISNOT *SQLNULL, or the Intrinsic Property .IsSqlNull.
- When using IF_NULL or .IsNull, an SQL Null field will return FALSE.
- Since SQL Null does not represent a value, when using an equality operator such as *EQ, *LE, *GT to compare fields, and one of the factors of the compare is SQL Null, the comparison will produce an SQL Null. When combined with *OR and *AND operators, an SQL Null factor will continue to produce an SQL Null. A conditional expression that produces an SQL Null will evaluate to false.
- SQLNULL comparisons will always stay as SQLNULL if the SQLNULL value is true. That is, when an expression is testing an SQLNULL, and there IS an SQLNULL, the expression will keep the SQLNULL value. For these types of scenarios, the *ORIF boolean feature should be used.
...
- If you want a condition to return TRUE for both Null and SQL Null, use the *ORIF boolean feature together with Intrinsic Property .IsSqlNull and Intrinsic Property .IsNull. The following condition on our sample field #A will return true if the field is zero or SQL Null.
...
- If you want a condition to return TRUE for both SQL Null and some other value, use the Intrinsic Method .AsValue. The following condition will return true if the field is 1 or SQL Null.
...
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).
...