U0100070: When GaussDB uses the plus sign (+) operator, a table cannot correspond to multiple foreign tables

Description

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB versions supported by UGO

Syntax Example

In the WHERE clause, you can use the operator (+) to convert a table join to an outer join. However, this method is not recommended because it is not the standard SQL syntax and may raise syntax compatibility issues during platform migration. There are many restrictions on using the operator (+):

1. It can appear only in the WHERE clause.
2. If a table join has been specified in the FROM clause, the operator (+) cannot be used in the WHERE clause.
3. The operator (+) can work only on columns of tables or views, instead of on expressions.
4. If table A and table B have multiple join conditions, the operator (+) must be specified in all the conditions. Otherwise, the operator (+) will not take effect, and the table join will be converted into an inner join without any prompt information.
5. Tables specified in a join condition where the operator (+) works cannot cross queries or subqueries. If tables where the operator (+) works are not in the FROM clause of the current query or subquery, an error will be reported. If a peer table for the operator (+) does not exist, no error will be reported and the table join will be converted into an inner join.
6. Expressions where the operator (+) is used cannot be directly connected through OR.
7. If a column where the operator (+) works is compared with a constant, the expression becomes a part of the join condition.
8. A table cannot have multiple foreign tables.
9. The operator (+) can appear only in the following expressions: comparison, NOT, ANY, ALL, IN, NULLIF, IS DISTINCT FROM, and IS OF. It is not allowed in other types of expressions. In addition, these expressions cannot be connected through AND or OR.
10. The operator (+) can be used to convert a table join only to a left or right outer join, instead of a full join. That is, the operator (+) cannot be specified on both tables of an expression.

Suggestion

You are advised to use an outer join, for example:

Source SQL:

 SELECT A.ID,B.NAME FROM TEST_KEEP A,TEST_KEEP1 B ,TEST_KEEP2 C WHERE A.ID=C.FATHERID(+) AND B.NAME=C.NAME(+);

Target SQL:

 SELECT A.ID,B.NAME FROM TEST_KEEP A CROSS JOIN TEST_KEEP1 B LEFT JOIN   TEST_KEEP2 C  ON  B.NAME=C.NAME AND A.ID=C.FATHERID;