2

I get this error from Progress database when running the following query using ODBC:

SELECT distinct Table.column,
        { fn CONVERT(SUBSTRING(Table.ProblematicColumn, 1, 60), SQL_VARCHAR)} as test
FROM PUB.Table
WHERE ( Table.id IN (
            SELECT Table.id
             FROM PUB.Table
            ) )

I know it's possible to fix it using the DBTools. However, I run queries against multiple Progress databases of multiple clients, so it's not practical to do this every time. Also, for some reason, the ODBC client I'm using (PHP), doesn't show any error when this happens. Instead, it returns an empty result.

The convert I did to a VAR_CHAR of 60 character did help until I added the sub-query. When the sub-query is there, I get again the same error.

Interestingly enough, when the 'distinct' is not there, it's working. But I do need the distinct.

Edit: The question is how can I execute this query without fixing the width column with DBTool.

David
  • 2,528
  • 1
  • 23
  • 29
  • 1
    So you know what the problem is and you know how to fix it. But you don't want to fix it. Help us out here... what is your question? – Tom Bascom Jul 01 '16 at 13:29
  • The question is simple - how to execute the query and avoid this error. As mentioned - using the `{fn CONVERT}` did help for the main query. How it can be fixed when there is a sub query involved – David Jul 01 '16 at 13:43
  • The answer to that question is also simple and well known to you. – Tom Bascom Jul 01 '16 at 13:50
  • Tom, I'm not following, are you refer to the answer you added of upgrading to 11.6? see my answer there. – David Jul 01 '16 at 14:19
  • 1
    The other way to implement this kind of constraint is to use `EXISTS` but I don't know if Progress supports this. BTW your question was invaluable in solving my problem, the solution to which I documented here. http://stackoverflow.com/questions/25851241/sql-ssis-import-error-progress-openedge-wire-protocol-column-in-table-has-value/43993049#43993049 If you still need a solution please post back and I can concoct SQL with an EXISTS clause for you – Nick.Mc May 16 '17 at 06:58

2 Answers2

3

It took a few minutes to find an answer. The problem appears to be in the OE10 SQL broker not handling the sub select in the where clause. This alternative using an inner join to a sub select looks to be equivalent to me. I tested it, it does work. Replacing the SQL client will do nothing, the error occurs in the OpenEdge SQL broker: I get the same error using the OpenEdge JDBC driver.

SELECT distinct Table.column,
   { fn CONVERT(SUBSTRING(Table.ProblematicColumn, 1, 60), SQL_VARCHAR)} as test
FROM PUB.Table inner join (select id from PUB.Table) t2 on Table.id = t2.id
John Kuhns
  • 506
  • 4
  • 20
  • Thank you John. Unfortunately, my specific case is more complex, the example I've mentioned here is a very simplified version of the real query. I tried your approach and moved the subquery from the WHERE to the JOIN, changed from INNER JOIN to LEFT JOIN because the sub-query part in the WHERE clause was 'OR' not 'AND'. Still, it doesn't expand the result-set and I believe this is because the WHERE clause narrows down the result-set. Anyway, your answer is 100% correct and moving the sub-query to the JOIN clause does resolve the error message. – David Jul 20 '16 at 17:04
0

Upgrade to OE 11.6.

There are options in 11.6 to automatically and silently truncate the data so that you will not get an error.

"Autonomous Schema Update"

https://community.progress.com/community_groups/openedge_rdbms/f/18/t/19534

Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
  • Thanks Tom. Unfortunately it will not be feasible for me to upgrade the database for all our clients. Also, I'm not sure they'll like the idea that their data is truncated silently. I'm wondering what was the idea of letting the user insert data more than the column width, but then when querying the data to show an error. I can understand the flexibility needed to add data more than the original width, but I would expect the width to be changed as well (similar to no-sql). – David Jul 01 '16 at 14:19
  • Changing the width on the fly is also an option. – Tom Bascom Jul 01 '16 at 14:58
  • If you want it to behave the way that you describe then you must either run dbtool or upgrade or find a SQL client that isn't so uptight about field width. – Tom Bascom Jul 01 '16 at 15:01
  • I hope there is an alternative. These two options are not practical in my situation. I provide a service for lots of clients who use Progress DB version 10. I can't update them all, and I can't run db-tools on their databases every time a user enters a long string. I don't even have an access to this db-tools. All I have is an ODBC connection, which many times is read-only. – David Jul 01 '16 at 15:25
  • Until unicorns start emitting rainbows at your clients those are your options. – Tom Bascom Jul 01 '16 at 17:34