OWB 11.2 Generates Cartesian Product / Cross Join Instead of Full Outer Join When using Non ANSI SQL Syntax (Doc ID 1319572.1)

Last updated on MAY 09, 2017

Applies to:

Oracle Warehouse Builder - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

An 11.2 Mapping with a JOIN condition:
"SOURCE_TABLE1.COLUMN1(+)=SOURCE_TABLE.COLUMN1(+)",
generates Cartesian Product when the ANSI SQL syntax property of the mapping is set to FALSE. (WHERE CLAUSE MISSING)

The generated code looks like this :

1. With ANSI SQL syntax property of mapping is set to FALSE

INSERT
INTO
"TABLE_3" "TABLE_3"
("COLUMN1",
"COLUMN1_1")
(SELECT
/* JOINER.OUTGRP1 */
"TABLE_1"."COLUMN1" "COLUMN1$2",
"TABLE_2"."COLUMN1" "COLUMN1_1$2"
FROM
"TABLE_1" "TABLE_1",
"TABLE_2" "TABLE_2"
)
;



==> no where clause generated


2. With ANSI SQL syntax property of mapping is set to TRUE

INSERT
INTO
"TABLE_3" "TABLE_3"
("COLUMN1",
"COLUMN1_1")
(SELECT
/* JOINER.OUTGRP1 */
"TABLE_1"."COLUMN1" "COLUMN1$2",
"TABLE_2"."COLUMN1" "COLUMN1_1$2"
FROM
"TABLE_1" "TABLE_1"
FULL OUTER JOIN "TABLE_2" "TABLE_2" ON ( "TABLE_1"."COLUMN1" =
"TABLE_2"."COLUMN1" )
)
;

Changes

Upgrade from OWB 10.2 to 11.2.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms