Skip to Content

Syntax OUTER JOIN IN where tab Query transform DATA SERVICES XI 3.2 VERSION 12.2.3.4

Dear All ,

I have a dataflow that works properly. This data flow load a Fact table of a Star.

I have put this join below In Query TRANSFORM in the where tab :

SQL_MANTIS_BUG_HISTORY_TABLE.bug_id = SQL_BUG_TABLE.id and mantis_custom_field_string_table.bug_id = SQL_BUG_TABLE.id

The Job works but the join is not perfect because I need an Outer join between the table mantis_custom_field_string_table and the table SQL_BUG_TABLE in order not to lose registers from mantis_custom_field_string_table that not match with SQL_BUG_TABLE

In Oracle the left join should be :

FROM SQL_BUG_TABLE

LEFT OUTER JOIN mantis_custom_field_string_table ON mantis_custom_field_string_table.bug_id = SQL_BUG_TABLE.id

INNER JOIN SQL_MANTIS_BUG_HISTORY_TABLE ON SQL_BUG_TABLE.id = SQL_MANTIS_BUG_HISTORY_TABLE.bug_id

But I do not know in data services how to implement a left join with an inner join using the tab WHERE and the tab OUTER JOIN

I attach pictures .

Please let me know the sintax for THE TAB WHERE and let me know please what I Imust include in the tab OUTER JOIN

Thanks in advance.

Best Regards

Antonio

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 26, 2017 at 12:50 PM

    It's high time you upgrade to a recent DS version.

    The way to define joins has changed in version 4.0 (i.e. since 2011!!!).

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 26, 2017 at 01:45 PM

    Hello Dirk ,

    I can not upgrade to a recent DS version 4.0 because I work for the public administration in Madrid (Spain)

    and it does not depend on me unfortunatly.

    Please let me know your how to solve this issue with my current version.

    Thanks in advance

    Best Regards

    Antonio

    Add comment
    10|10000 characters needed characters exceeded

    • I am sorry, I really don't remember. Too long ago.

      Your where-clause looks good to me.

      I assume you need mantis_custom_field_string_table as Outer source in the OUTER JOIN tab and the other tables as Inner source.

      If that doesn't work, I would use 2 query transforms, the 1st for the outer join. Then in the 2nd transform, inner join the results from the 1st with the 3rd table.

  • Oct 27, 2017 at 08:29 AM

    Hi.

    For the time being you will have to break the join into 2 query trasnform.

    First Query Transform: Do the inner join and bring the columns you need to load in the target. In addition to this bring the attribute that you will be using for outer join

    Second Query Transform: Use the third source and the attribute that have have brought forward in first query transform to do an outer join.

    Beware: In case of big source tables this method will test your job server capacity and you have to adopt additional performance optimization techniques. As suggested by Dirk you should now push for an upgrade.

    Shaz

    Add comment
    10|10000 characters needed characters exceeded

    • Even his current implementation may put a heavy load on the DS server. It's a very bad idea to join SQL Transforms in a dataflow, as the join logic cannot be pushed down to the underlying databases. DS will have to pull all content into memory before performing the join.