Custom DATE objects invalid when data in row is not present

[Former Member]

HI:

Environment: Using BO Xi 3.1 in a windows environment hitting a iSeries (AS/400 DB) V5R3M0

Problem: Our universe got several customs DATE objects that convert several TEXT fields into DATE objects. Under those date objects the universe have the regular week, quarter and year details.

The date objects are something like this:

DATE( FieldTXT1 || '/' || FieldTXT2 || '/' || FieldTXT3)

That returns a valid date when FieldTXT1 has a Month, FieldTXT2 has a Day and FieldTXT3 has a Year. || means concact in AS/400 query lenguage.

BUT!!! When those fields (FieldTXT1 ...FieldTXT3) are empty then the DATE() function breaks and do not parse.

I could fix that setting the DATE object as a CHARACTER type, but if I do that then the detail objects from Hierarchy (week, year, quarter) will break.

There is a way you may suggest for me to use that returns an empty DATE column for everyone of the custom objects involved when the fields involved in the concact are empty?

Thanks in advance for any advice !!!

Reinaldo

[former_member88971 · Accepted solution]

Reinaldo,

You can use a case statement to check if FieldTXT1, FieldTXT2 or FieldTXT3 are empty, the code looks like below

Case when date(FieldTXT1) > 0

then DATE( FieldTXT1 || '/' || FieldTXT2 || '/' || FieldTXT3)

else 0

end case

However I,m not sure if the above is the correct syntax for DB2, but I,m sure DB2 knows the case statement.

Regards

Raymond

[Former Member · Accepted solution]

Raymond, That is a good answer. We have it implemented in about half of our SQL DATE objects. The issue beging to be big when the FIELDtxt1 is >0 but is also >31 (for a day) or > 12 (for a month)...for those silly cases the DATE () function breaks because is invalid or out of range.

Thanks for your time, I am considering it passing the issue to the application team (the one that feeds the DB)

Thanks again

[former_member88971 · Accepted solution]

Looks like a data cleaning issue to me, it is almost impossible to handle all acceptions if you fix this one what will be next.

Good luck

Raymond