Article

Oracle error when running a query using INDEX and OUTER JOIN

Information

 
Article Number000091480
EnvironmentProduct: OpenEdge
Version: 11.7.x
OS: All supported platforms
Other: DataServer for Oracle
Question/Problem Description
ABL query that uses the INDEX function and OUTER-JOIN fails with an Oracle error.

Oracle errors include:
ORA-00903 ** invalid table name
ORA‌-00933: SQL command not properly ended


Table list and join is malformed when the INDEX function is in place.

Table list incorrectly includes a section from the WHERE clause representing the INDEX function.

Examples of incorrect WHERE clauses extracted from dataserv.lg:

... FROM SCHEMA.TABLE0 T0,('''' || TO_CHAR((T0.FIELD1)) || '''')) > 0 AND T0.FIELD2 >= 1 AND T0.FIELD3 IS NULL LEFT OUTER JOIN SCHEMA.TABLE1 T1 ON (T0.FIELD4 = T1.FIELD1) ...

... FROM OE117.ORDER_ T0,upper(('''' || TO_CHAR((upper(T0.INSTRUCTIONS))) || ''''))) > 0 LEFT OUTER JOIN OE117.CUSTOMER T1 ON (1 = 1) ...
 
Steps to ReproduceMigrate a copy of Sports2000 to Oracle.
Run the attached code, sportsrepro.p, to reproduce error ORA‌-00933: SQL command not properly ended.

The code uses the following query syntax:

q = "FOR EACH b_Order FIELDS(ordernum custnum)
WHERE INDEX(~"suske~":U, ~"'~":U~ + STRING(b_Order.instructions) + ~"'~":U) > 0,
EACH b_Customer FIELDS(custnum) OUTER-JOIN
QUERY-TUNING(NO-BIND-WHERE JOIN-BY-SQLDB)".
Clarifying Information
Using DataServer for Oracle.

Error does not occur in OpenEdge 11.6.x; records are returned as expected.

Problem does not occur if the OUTER-JOIN option is removed from the query.

Problem does not occur if the join is performed on the client-side (NO-JOIN-BY-SQLDB).
Error MessageORA-00903 ** invalid table name

ORA‌-00933: SQL command not properly ended
Defect/Enhancement NumberDefect ODIA-2171
Cause
The exact cause is not known at this time. The problem is specific to the use of the INDEX function.
Resolution
Until the fix is available, use the workaround below.
Workaround
Using the LOOKUP function instead of INDEX avoids the issue.
Notes
Attachment
Last Modified Date9/17/2018 10:41 AM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025