Hello all,
I have posted a similar question before…
but i havent found an answer yet. So…
I am trying to do a full outer join in BO 5.1.8. I am trying to join 2 tables that have 3 common dimensions and i want to get rows that are in table A that are not in Table B and, rows that are in Table B and not in Table A (Full outer join)
I cannot do a free hand SQL. I do have rights to modify the universe though.
That isn’t a full outer join that is the union of two minus queries. While very slow you can do that with subqueries and using Not In List. How many records are you talking about?
I do not think that you need a full outer join. A full outer join between two tables returns all rows from both table. This is not what you have described as your needs. Correct?
Yes, I dont want to see the common records. I only looking for the exceptions (combo of the 3 fields present in one table nut not in the other).
Table A has about 80,000 records and Table B has about the same number.
What i’m trying to do is an exception report, so ideally there shoudlnt any orphan record.
That is going to be tough without the Freehand SQL. This will do it but is just about as inefficient as you can get.
What you can do is build an object for each table which is the concatenation of the key information. Lets say Job and Year so Job || Year.
Then you have a report with the fields from Table1 where the object from Table1 is not in list and then write the subquery for it which selects the object from Table2. Now do a union of the exact opposite. That will do it but could take a long time as table scans are needed for each loop.
PS: Is there anyone who can write the Freehand SQL for you?
The MINUS operator works. I was to accomplish the desired results using the minus operator.
Thanks everyone.
P.S: I just notied that when using the MINUS operator we cannot change the objects that retrieved by the two SQL statements. They have the same objects and not just objects of same datatype (like in regular SQL).