Outer Join Work Around?
Outer Join Work Around?
(OP)
I don't know if there is a good outer join work around when your not able to select the outer join option in the link between two different data sources. I'm trying to do a full outer join between a SQL database and SAP BW data.
I thought about doing two left join sub reports but where I'm stuck at is how I would eliminate the duplicates. Any ideas is appreciated.
I thought about doing two left join sub reports but where I'm stuck at is how I would eliminate the duplicates. Any ideas is appreciated.
RE: Outer Join Work Around?
Are you able to use other kinds of joins?
Also it would help to see some sample data and a sample of the results you would like to achieve.
-LB
RE: Outer Join Work Around?
Sample data:
Database 1
Name Hours Rate Cost
Jim 5 $5 $25
Bill 10 $2 $20
Mike 6 $8 $48
Phil 7 $9 $63
Mark 8 $10 $80
Jeff 5 $5 $25
Database 2
Name Hours Rate Cost
Jim 4 $6 $24
Blake 3 $6 $18
Philbo 6 $9 $54
Phil 7 $11 $77
Mark 8 $10 $80
Archie 6 $8 $48
Below is where a full outer join would come to play but it's not available to use.
Name Cost DB1 Cost DB2 Variance
Jim $25.00 $24.00 $1.00
Bill $20.00 $0.00 $20.00
Mike $48.00 $0.00 $48.00
Phil $63.00 $77.00 -$14.00
Mark $80.00 $80.00 $0.00
Jeff $0.00 $0.00 $0.00
Blake $0.00 $18.00 -$18.00
Philbo $0.00 $54.00 -$54.00
Archie $0.00 $0.00 $0.00
RE: Outer Join Work Around?
-LB
RE: Outer Join Work Around?
RE: Outer Join Work Around?
That's all assuming I had drivers set up for each db. If I had to set up new drivers in the ODBC Manager, it might take an additional 15 minutes.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Outer Join Work Around?
Select db1.field1
From owner.db1 db1
Union all
Select db2.field1
From owner.db2 db2
Otherwise, if you could get the data from each database into Excel or access, as Skip suggests, you could then reference them in a command using a union all in such a way you could identify discrepancies in both directions.
-LB
RE: Outer Join Work Around?
If you do decide this route, please post any questions regarding Excel in forum68: Microsoft: Office.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Outer Join Work Around?
RE: Outer Join Work Around?
-LB
RE: Outer Join Work Around?
RE: Outer Join Work Around?
However, I'm guessing if you can't use the two databases in a command (did you try the union statement?), then you might not be able to join the two databases regardless of the type of join.
-LB
RE: Outer Join Work Around?
Can you explain more about possibly passing a array string to the 2nd subreport to suppress the duplicates?
RE: Outer Join Work Around?
-LB
RE: Outer Join Work Around?
Also, if there are selection criteria that don't appear in the SQL query, can you paste the selection formula here also? Thanks.
-LB
RE: Outer Join Work Around?