4 Replies Latest reply: Aug 19, 2013 4:24 PM by SIVA POREDDY RSS

Problem with Left outer Join in query in BODS

SIVA POREDDY
Currently Being Moderated

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.

  • Re: Problem with Left outer Join in query in BODS
    Arun Kumar
    Currently Being Moderated

    Siva,

     

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

     

    Arun

    • Re: Problem with Left outer Join in query in BODS
      SIVA POREDDY
      Currently Being Moderated

      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

      • Re: Problem with Left outer Join in query in BODS
        Arun Kumar
        Currently Being Moderated

        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

        • Re: Problem with Left outer Join in query in BODS
          SIVA POREDDY
          Currently Being Moderated

          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.

Actions