Skip to Content
author's profile photo Former Member
Former Member

Problem with Left outer Join in query in BODS

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 13, 2014 at 04:57 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 16, 2013 at 12:31 PM

    Siva,

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

    Arun

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Aug 12, 2014 at 10:28 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 18, 2014 at 04:55 PM

    Hi Siva,

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

    Many thanks,

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Sep 07, 2015 at 10:34 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.