cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with Left outer Join in query in BODS

0 Kudos

Hello,

I am using a LEFT OUTER JOIN in a Query where in the output of the query should result in records from LEFT table and exclude records from right table.

e.g.

Query IFLOT_AB contains contains Functional location data (TPLNR, PLTXT, FLTYP etc) and TABLE IFLOT_X contains (TPLNR) that contains EXCLUDED functional locations.

In the query I used a LEFT OUTER JOIN on IFLOT_AB.TPLNR = IFLOT_X.TPLNR and in where condition where IFLOT_X.TPLNR is NULL.

This should obviously return the records from IFLOT_AB that should not contain any record from IFLOT_X.

However, BODS is returning all the records from IFLOT_AB. The underlying data base we are using SQL server 2008. If I also include a dummy table from the first query, then the output of the final table is correct. (As of now I have used a script in BODS that contains a SQL subquery to delete records)

The same query I have tried writing in SQL server with the following code. The query is returning excluded records from IFLOT_AB. Remember that this query is on two tables.

SELECT A.MANDT, A.TPLNR, B.TPLNR FROM IFLOT_AB A LEFT OUTER JOIN IFLOT_X B

ON A.TPLNR = B.TPLNR WHERE B.TPLNR IS NULL

Can some one pls clarify what could be the issue with BODS.

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Siva,

Please let us know how did you resolve this issue.

We are facing same problem.

BODS is returning all records from left table not the unique ones.

Thanks in advance.

Regards,

Sarv

Former Member
0 Kudos

Hello -

I had the same problem but finally resolved using DATA TRANSFER TRANSFORM.

Please see this http://solveissue.com/note?id=1930808

I hope this helps.

Regards,

Bhargavi

Former Member
0 Kudos

Hi Siva,

We are facing a similar issue. Is it resolved for you?

Many thanks,

former_member208363
Active Participant
0 Kudos

Hi Shiva,


Hi Siva, Refer Balamurugan  reply.


It will help. Otherwise open a new thread.

Please change your design like below.

1. Join two tables using  LEFT OUTER JOIN on IFLOT_AB.TPLNR = IFLOT_X.TPLNR and add IFLOT_X.TPLNR into query mapping.

2. After join condition add one more query transform and in where condition where IFLOT_X.TPLNR is NULL. Now  you will get a list of values from left table which are not in the right table.

Thanks & Regards,

Balamurugan G

0 Kudos

Hi Anitha,

Yes you could use the solution proposed by by Bala and it works.

However, this solution works only when the query of outer join is placed immediately after the the two source tables.

In my case, when I used the outer join after a series of transforms with a resultant query and another table (with extra query for where null), the data services still not excluding the records from outer table so I had to segregate the logic into separate data flow.

hope this helps.

Former Member
0 Kudos

Hi Siva,

Please change your design like below.

1. Join two tables using  LEFT OUTER JOIN on IFLOT_AB.TPLNR = IFLOT_X.TPLNR and add IFLOT_X.TPLNR into query mapping.

2. After join condition add one more query transform and in where condition where IFLOT_X.TPLNR is NULL. Now  you will get a list of values from left table which are not in the right table.

Thanks & Regards,

Balamurugan G

Former Member
0 Kudos

I am experiencing this same problem. NULLs caused by lack of a right row in a left outer join are not detected by IS NULL in the where clause.

Former Member
0 Kudos

You cannot do a join comparing NULL values with NON NULL values. What you should do is change all the NULL to some values using nvl() and compare this with the NON NULL values during join.

Former Member
0 Kudos

Siva,

You are using an outer join with condition NULL, which won't work. Modify the query to include non-null values.

Arun

0 Kudos

Hi Arun,

My requirement is to get a list of values from left table which are not in the right table.

I am not joining with condition NULL but LEFT JOINING on TPLNR and then applying right table value is NULL.

Obviously when you use LEFT OUTER JOIN, if there is no entry in RIGHT table, the query should give right table values NULL.

Siva

Former Member
0 Kudos

Siva,

Are you making the value in right table as NULL for join? You don't have to make it NULL to carry forward. What your query does is join two tables with the join condition where one of the joining field is NULL, basically it is searching for a field on other table where the column is equal to NULL.

Arun

0 Kudos

Thank you for your time in replying to my question. However, I hope you understood my original question correctly.

This is just to remove records from table IFLOT_AB by comparing the records in IFLOT_X. A general and proven way to do this in SQL is using a LEFT OUTER JOIN. (In this case first a join would be applied based on the ON condition and since a LEFT OUTER JOIN is applied, if there is no matching record found in TABLE IFLOT_X, the value from IFLOT_X would become NULL. Then a where condition applied with TPLNR = NULL. So, the resultant records from this query is from IFLOT_AB that are excluded from IFLOT_X). The following SQL statement works perfectly and no issues in SQL server 2008)

SELECT A.MANDT, A.TPLNR FROM IFLOT_AB A LEFT OUTER JOIN IFLOT_X B

ON A.TPLNR = B.TPLNR WHERE B.TPLNR IS NULL

However, When the same is applied in BODS as per the attached screen shot,  this is not working.