The original OUTER join syntax of Informix® is different from Microsoft™ SQL SERVER outer join syntax:
In Informix SQL, outer
tables can be defined in the
FROM clause with the
OUTER keyword:
SELECT ... FROM cust, OUTER(order)
WHERE cust.key = order.custno
SELECT ... FROM cust, OUTER(order,OUTER(item))
WHERE cust.key = order.custno
AND order.key = item.ordno
AND order.accepted = 1
Microsoft SQL SERVER
supports the ANSI outer join syntax:
SELECT ... FROM cust LEFT OUTER JOIN order
ON cust.key = order.custno
SELECT ...
FROM cust LEFT OUTER JOIN order
ON cust.key = order.custno
LEFT OUTER JOIN item
ON order.key = item.ordno
WHERE order.accepted = 1
Remark: The old way to define outers in SQL SERVER looks like the
following:
SELECT ... FROM a, b WHERE a.key *= b.key
See the SQL SERVER reference manual for a complete description
of the syntax.
Solution
For better SQL portability, you
should use the ANSI outer join syntax instead of the old Informix OUTER syntax.
The Microsoft SQL SERVER interface
can convert simple Informix OUTER
specifications to Microsoft SQL
SERVER ANSI outer joins.
Prerequisites:
- The outer join in the WHERE part must use the table name as prefix.
Example: "WHERE tab1.col1 = tab2.col2 ".
- Additional conditions on outer table columns cannot be detected
and therefore are not supported: Example: "... FROM tab1, OUTER(tab2)
WHERE tab1.col1 = tab2.col2 AND tab2.colx > 10".
- Statements composed of 2 or more SELECT instructions using OUTERs
are not supported. Example : "SELECT ... UNION SELECT" or "SELECT
... WHERE col IN (SELECT...)"
Remarks:
- Table aliases are detected in OUTER expressions. OUTER example
with table alias: "OUTER( tab1 alias1)".
- In the outer join, <outer table>.<col> can be placed
on both right or left sides of the equal sign. OUTER join example
with table on the left: "WHERE outertab.col1 = maintab.col2 ".
- Table names detection is not case-sensitive. Example: "SELECT
... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".
- Temporary
tables are supported in OUTER specifications.