BODS 3.2 Outer Join causing a Cartesian Join

[brad_schroeter2]

I have two input schemas in my Query_Join transform: one is named "PCU_LT_CM_PCC_MSNG_RCRDS" which is a source table of 8,014 records (no BODS field marked as primary key), and the other is named "SQL" which is a SQL transform of 467,078 records (BODS field SQL.PCCID marked as primary key).  The Query_Join transform has the OUTER JOIN tab with Outer source = PCU_LT_CM_PCC_MSNG_RCRDS and Inner source = SQL, and the WHERE tab with "SQL.PCCID = PCU_LT_CM_PCC_MSNG_RCRDS.SRC_SYS_RCRD_ID_VAL" (SQL.PCCID is primary key and thus a unique value; PCU_LT_CM_PCC_MSNG_RCRDS.SRC_SYS_RCRD_ID_VAL is a non-unique value).

For some reason, it's performing a Cartesian join instead of an outer join.  I've tried numerous ideas but I cannot get it to work.  Any suggestions?

Thanks,
Brad

[brad_schroeter2]

What's interesting is that I was encountering a cartesian job when I was originally using a source table and a SQL transform for the outer join.  When I switched the SQL transform to a second source table (which has similar fields to the SQL transform), the outer join is working properly.

Thanks,

Brad

[Former Member]

I am curious why you used a SQL transform at all? You lose meta data, lineage, impact analysis and push down capability.

In any case, if you must use a SQL transform (at a certain complexity level it can become desirable, such as nested 4 deep sub selected from clauses), do all the work in the SQL transform, including any joins.

- Ernie

[Former Member]

Do you have any other reference field that establishes a relation ship? Like in where tab WHERE X =Y and "Some other relationship field" would solve your problem.

If you are expecting only unique record output, may be you can try lookup_ext() to solve the issue.

Arun

[Former Member]

Could you please get the SQL which is been genered by BODS from

Validation->Display Optimized SQL and check are you getting the duplicates from the source itself or please check is there any other relaitonship

Regards,

Raji