My DB2 table contains a 6000 charac

[curtispell]

My DB2 table contains a 6000 character field of which every 12 characters represents an error code that looks something like 06-567-09R01. There can be up 500 errors per item. I need to count the number of errors per item in Business Objects. Also, I need to be able to count, as an example, the number of occurrences of the error code 06-567-09R01 in the entire table.

Can anyone help on how to approach this problem?

 

[blom0344]

If your field really holds data up to 6000 characters I suspect it is a CLOB (as it is not a binary string). I think that the designer manual states that BusObj does not work with these datatypes.
Did you try to use the DB2 function LENGTH to check the total length of the string in the field. Or something like LENGTH(RTRIM()) ..........
Perhaps you should split the field to appropiate segments once you know the maximum length that occurs using a database view.....
T. Blom
Information analyst
tbl@shimano-eu.com

 

[curtispell]

Thanks for the quick reply.

I understand that I can use the database manager or an ETL like Informatica or SAS to create a view or table that has the rows populated with the item id and error code. This view or table would then be used by Business Objects to either count the errors or use a filter to get counts for a particular error code. I have done this and it works quite well.

What I was hoping for was a one tool solution using BO, particularly since a resident expert here at work continues to say that we can do everything we need to do in Business Objects. So far, I have not found that using only BO will solve this problem.

Incidently, the DB2 6000 byte field is a varchar field.

Thanks for the help,
Curt

 

[blom0344]

I thought varchar could contain up to 4000 characters, but maybe you are already using DB2 version 8 which possibly can contain more for the varchar datatype.........
I think it is a bit naive to presume that a certain tool would be suited for any case and your case is not exactly a very standard one.

Creating specific database views to tackle problems with tools outside the database is quite common practice. In some cases it is even the preferred way to go. Such a view can be added to the the BO universe and be used for reporting purposes........

Check out:

http://www.forumtopics.com/busobj/

for a lot more expertise T. Blom
Information analyst
tbl@shimano-eu.com