Description Henning von Bargen 2013-12-03 12:28:53 EST

We ran into the following error several times for some master-detail reports:

The master SQL has an output column (let's call it N for now) of SQL type NUMBER, which BIRT treats as "Decimal".

The detail SQL uses this value as an input parameter (to be used in the WHERE clause).
It is possible that this value is NULL.

We are using "String" for DS input parameters, because otherwise we cannot assign a default value to the parameter.

In the table item corresponding to the detail data set, we are using the following expression for the dataset parameter binding: nvl_mt(row["N"])

The JS function nvl_mt(x) (read: nvl empty) is defined as:
function nvl_mt(x) {
  if (x == null) {
    return "";
  }
  return x
}

When running the report, we occasionaly get an SQL exception "ORA-01722 Invalid Number" or wrong results, depending on the data from the DB and the NLS setting, as it seems.

With some debug logging added, we then found that BIRT added a thousands separator  character "." for values greater than 999 (e.g. "1.000"), which then caused the DB to bark.

Note: The NLS environment is sometimes German, sometimes US, thus we cannot assume this is fixed.

I know that it is our own mistake in first place, but the point is:

We are using the nvl_mt function as a work-around for the following BIRT error:

If any dataset parameter is null, BIRT spits out tons of stack trace and errors when executing the query, like this:
SEVERE: dataset parameter 4 cannot be null

The correct solution of course would be:

Use the correct data type in the report definition (in this case: Integer)
BIRT allows null parameter values in DS parameters.

I don't see any reason why null should not be allowed. The only obvious reason would be determining the parameter's data type at runtime (in order to call the correct JDBC API, e.g. setInteger, setString or whatever), but this is not at all necessary, because the developer *already defined* the dataset parameter's data types at report design time.

See also bug #351693 and http://www.eclipse.org/forums/index.php/t/62611/, .../65570/, .../46196/