on 08-16-2013 11:53 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi Siva,
We are facing a similar issue. Is it resolved for you?
Many thanks,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Siva,
You are using an outer join with condition NULL, which won't work. Modify the query to include non-null values.
Arun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.