Kbase P174376: SQL query accessing a View fails with error 10713 after upgrade to OpenEdge 10.1x or later
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
01/10/2010 |
|
Status: Unverified
SYMPTOM(s):
SQL query accessing a View fails with error 10713 after upgrade to OpenEdge 10.1x or later
SQL query accessing a View fails after upgrade to OpenEdge 10.1x or later
Syntax error at or about (statement excerpt). (10713)
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210056
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "level, 1, 3) = acclevel_1.level join pu" (10713)
SQL query references a View
SQL query executes as expected in Progress 9.1x
SQL query fails in OpenEdge 10.1x or later
SQL statement that creates the View works in Progress 9.1x
SQL statement that creates the View fails in OpenEdge 10.1x and later
FACT(s) (Environment):
Database was migrated using CONV910
SQL View statements are stored in the database
OpenEdge 10.1x
OpenEdge 10.2x
All Supported Operating Systems
CHANGE:
Upgraded from Progress 9.1x
CAUSE:
A field name has been designated as a reserved word between version. SQL reserved words cannot be used as an identifier for constants, variables, cursors, types, tables, records, subprograms, or packages from OpenEdge 10.1x onward. SQL reserved words must be encapsulated in quotation marks in order to be used in a SQL statement.
The View and its syntax was migrated "as-is" from Progress 9.1x to OpenEdge 10.1x/10.2x so the View statement is unchanged, leaving the SQL reserved word without quotation marks. The query fails as a result.
FIX:
1) Backup the database
2) Drop the SQL View:
DROP VIEW <schema>.<ViewName>
DROP VIEW pub.myView
3) Correct the syntax of the SQL statement that creates the View to ensure that any reserved words are encapsulated in quotation marks
4) Re-run the ?create view? statement to re-add the View to the database
5) Test the View