Skip to Content
0

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

Oct 26, 2017 at 09:06 AM

56

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Dirk Venken
Oct 26, 2017 at 12:50 PM
0

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!!!).

Share
10 |10000 characters needed characters left characters exceeded
Antonio Astudillo Oct 26, 2017 at 01:45 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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.

0
Shazin Siddiqui Oct 27, 2017 at 08:29 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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.

0