ODBC link to a progress database produces data Cognos cannot use

[shouldknowbetter]

Hello Cognos Community,

We are using an ODBC driver supplied by OA to interrogate their progress database. The data returned includes 1 field that then has separators within it. There are actually 15 periods of data all squashed into this one field. Any idea on how we parse that data within SQL, or are we doing something dumb?

Thanks,

[Danno]

If that field is consistent then you could use a series of embedded substring functions to separate out the data. So it is doable but completely dependent on the tools you are using. I would be looking closer to the data provider to find out if there is a better mechanism to separate this out. If this were in Oracle and the system outputting the data had no other way to output this but in a single field I would be tempted to use PL/SQL to parse the data into another table, automate that, then consume it.  But try substring first before reinventing the process.

Just my pre-coffee $0.02 worth....

[shouldknowbetter]

Thanks for your suggestion.

The eventual solution was to add a form of split function in. The field was called PeriodBal. It had about 12 ;separated values within it. We simply added PeriodBal[1], PeriodBal[2],....PeriodBal[12] to the selection query. However, that caused Cognos to crash. The helpful (for once) error in the error log pointed to a known issue at IBM, in that the ODBC driver to Progress returns row counts in the wrong format. However, if run from a macro the counts are not returned. That all worked.

Phew, and thanks for your help anyway. Luckily I didn't need to do that, because I'm not sure I could have.

Paul.

[Danno]

Glad it worked out and I am even more glad you shared your resolution   :D