Transformation Guide

Back Next

Common Database Syntax Restrictions

Different databases have different restrictions on outer join syntax. Consider the following restrictions when you create outer joins:

Do not combine join conditions with the OR operator in the ON clause of outer join syntax.

Do not use the IN operator to compare columns in the ON clause of outer join syntax.

Do not compare a column to a subquery in the ON clause of outer join syntax.

When combining two or more outer joins, do not use the same table as the inner table of more than one outer join. For example, do not use either of the following outer joins:

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA TABLE3 LEFT OUTER JOIN TABLE2 ON TABLE3.COLUMNB = TABLE2.COLUMNB }
{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA TABLE2 RIGHT OUTER JOIN TABLE3 ON TABLE2.COLUMNB = TABLE3.COLUMNB}

Do not use both tables of an outer join in a regular join condition. For example, do not use the following join condition:

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA WHERE TABLE1.COLUMNB = TABLE2.COLUMNC}

However, use both tables in a filter condition, like the following:

{ TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMNA = TABLE2.COLUMNA WHERE TABLE1.COLUMNB = 32 AND TABLE2.COLUMNC > 0}

Entering a condition in the ON clause might return different results from entering the same condition in the WHERE clause.

When using an alias for a table, use the alias to prefix columns in the table. For example, if you call the REG_CUSTOMER table C, when referencing the column FIRST_NAME, use “C.FIRST_NAME.”

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Common Database Syntax Restrictions

Common Database Syntax Restrictions