Wednesday, August 8, 2007

WBI ESQL - only 1 level of function call?

The forecast data exported by i2 are fixed width decimals (10,3)
e.g. WK1=' 123.000', WK2=' 89.000', etc.
.000 is redundant as they only contain integer data.

So the ESQL code to insert them into an Oracle table with Integer fields was:

PASSTHRU('INSERT INTO ...',
CAST(TRIM(WK1) AS INTEGER),
CAST(TRIM(WK2) AS INTEGER),
...);
The idea is simple.
TRIM(' 123.000') becomes '123.000' and then it is CASTed as integer 123.
You cannot call CAST(' 123.000' AS INTEGER) directly as the leading spaces will throw errors.

However, this always silently failed. No records were inserted into Oracle.
The trace shows:
Evaluating expression TRIMBOTH( BOTH FROM ' 123.000')
The result was '123.000'
But where is the CAST?
It looks like WBI is not capable of evaluating further and call CAST.

I had to change the code to:

DECLARE pattern CHARACTER '##########.000';
PASSTHRU('INSERT INTO ...',
CAST(WK1 AS INTEGER FORMAT pattern),
CAST(WK2 AS INTEGER FORMAT pattern),
...);

Then the trace becomes:
Evaluating expression CAST( ' 123.000' AS INTEGER FORMAT '##########.000')
The result was 123
and everything worked.