10 Replies Latest reply: Nov 19, 2014 1:09 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.

Actions