DAI 0002370, reference X4 error with DB2, building report

[samuel]

Helo guys

Environment: AI 6.1 accessing DB2 9.1 on Windows
I get this error some times

Dynamic AI can't open the list Movgrupoprod - first 20 columns:

Connecting to the database and getting results seems ok!, the executed SQL seems valid!
However a problem arose during preparation of the Report output (reference: X4).

DAI 0002370

To determine when it occours, I removed all fields from the report construction, and I added the fields again one by one.

The error occours when I add a DATE field, that has empty values.
This field on DB2 is not flaged as NULLABLE.

Selecting the table by some other application, this date field returns with the following value "0001-01-01". This date field is not required on this table. That's why some rows has empty date values on this field. It's NOT NULL date value, because if I run this SQL:

Code: Select all

SELECT * FROM PRODUCTS WHERE LAST_SALE IS NULL

It returns no records.
But this one WHERE LAST_SALE = '0001-01-01', brings all records that has not a valid date on this field.

Is There a workaround on it?

Thanks

Samuel Pizarro
Estância Árvore da Vida

[admin]

Hi Samuel,

The date value '0001-01-01' can't be accessed thought ADO. The best fix I can give you right now, is to convert it to a string using either the built in functions, or adding a SQL column CHAR(LAST_SALE) or using a view with a string version (such as CHAR(LAST_SALE))

It would also be possible to evaluate the date value in the SQL and return NULL such as

Code: Select all

CASE
WHEN CHAR(LAST_SALE) = '0001-01-01' THEN
   null
ELSE
   LAST_SALE
END AS LAST_SALE_2


Hope that helps

Best regards
Bo

[samuel]

Dear Bo

So, If I also flag this field as Nullable on database, do you think it will work?

Thanks!

Samuel Pizarro
Estânica Árvore da Vida

[admin]

Hi Samuel,

I can't give you a clear answer on that.
If you allow nulls on the column, you will still have 0001-01-01 date values in the database. If you update those to null, it would work, but might "break" other applications accessing the same data.

I understand that it's an old DB2 convention to use 0001-01-01 as null and that applications might expect that value.

So if other users or programs are accessing the data, I suggest that you access it using a view that replaces 0001-01-01 with real null values.

Best regards

Bo

[samuel]

Hi Bo

Thanks for your kindly reply

The view with the case really resolves this issue.

Best regards

Samuel Pizarro
Estância Árvore da Vida