Category:

Database related

General

Returns a substitution value if the field value is null.

Syntax

dataset_nvl(
  StepNo Integer,
  FieldName String,
  SubstVal <FieldType> 
): <FieldType>
dataset_nvl(
  StepNo Integer,
  FieldIndex Integer,
  SubstVal <FieldType>
): <FieldType>

Parameters

StepNo specifies the step (pass) in which the function must be calculated. Possible values: 1 or 2. When it equals to 1, the corresponding function will be calculated immediately when the expression is processed. When it equals to 2, report will run in double-pass mode and the corresponding function will be calculated only in the second pass of the report processing, i.e. after the report dataset reaches the last record. Using database functions in the second pass is useful, for example, when it is needing to put the report totals in the report or group headers.

Combining different StepNo parameters in one expression (for example, database_field_val(1, 'total_sum') + database_field_val(2, 'payment_sum')) is possible, but such expression should be written so that functions with smaller StepNo will be calculated earlier.

FieldName is the name of the field which value to check.

FieldIndex is the field sequence number in the field mappings collection (data exporting context) or in the current dataset (reporting context).

SubstVal is the value to return if the field value is null. The type of SubstVal must match the type of the field.

Return value

Type: <FieldType> (depends on the database field type). Returns SubstVal, when value of the field is null, and the field value in another case.

Examples of using

dataset_nvl(1, 'Salary', 0)