concatenating array fileds

[Paul123]

Hi,

I'm new to the list and to SQL-92 and hope someone can set me on the right path. I am trying to concatenate some character array fields from our old Progress database to make them accessible and readable through ODBC. Ultimately I will want to make a view for this but at the moment am experimenting with some expressions to see what works.

The following is what I have so far:

SELECT PRO_ELEMENT(ArrayField,1,1) +
PRO_ELEMENT(ArrayField,2,2) +
PRO_ELEMENT(ArrayField,3,3) AS 'Contents'
FROM PUB.SomeTable;

-the above works but leaves no space between the arrays and I seem to running up against a field size limit (I need room for 1000 characters).

SELECT
RPAD(PRO_ELEMENT(ArrayField,1,1), LENGTH(PRO_ELEMENT(ArrayField,1,1)) + 1) +
RPAD(PRO_ELEMENT(ArrayField,2,2), LENGTH(PRO_ELEMENT(ArrayField,2,2)) + 1) +
PRO_ELEMENT(ArrayField,3,3)
FROM PUB.SomeTable;

-this one generates the following error:

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20152
[JDBC Progress Driver]:Character string is too long (8184)

This could be the incentive to finally get rid of those array fields, but any other help on this would be appreciated.

thanks,

Paul

 

[Paul123]

I'm off to a good start, answering my own thread, but this seems to solve the space character issue but am still running up against the size limit.

SELECT concat(PRO_ELEMENT(ArrayField,1,1) , ' ') +
concat(PRO_ELEMENT(ArrayField,2,2) , ' ') +
PRO_ELEMENT(ArrayField,3,3)
AS 'Contents'
FROM PUB.someTable;

I can't see how to set this apart from creating a new table.

Paul