Outer Join Work Around?

1tufgt · Apr 20, 2020

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.

lbass · Apr 20, 2020

Can you explain a little more about what you are trying to achieve?

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

1tufgt · Apr 20, 2020

What I'm trying to do is create a report that reads from 2 data sources and create a exception report that would display what is missing in each system. That would need to be fixed.

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

lbass · Apr 20, 2020

Are you able to reference both databases in the same command? If so, you could use a union all statement which I could help you set up.

-LB

1tufgt · Apr 20, 2020

Unfortunately I don’t think that’s possible since both DBs are not SQL DBs.

SkipVought · Apr 20, 2020

If I were facing this dilemma, and I have done such with data from different dbs, assuming we're not dealing with over 1 million rows, I'd query each db in Excel, each into a separate sheet and the on a third sheet, query the other two sheets/tables using UNION ALL. Might take 15 minutes.

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

lbass · Apr 20, 2020

I am unsure whether it is possible, but you could do a quick test. Something like:

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

SkipVought · Apr 20, 2020

You can do outer joins in Excel if necessary.

If you do decide this route, please post any questions regarding Excel in forum68.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

1tufgt · Apr 20, 2020

The requirement was to do it within CR but it looks like it’s not going to be possible.

lbass · Apr 20, 2020

If you get the data into Excel or Access, you could then use the Crystal Reports command object to get the desired result.

-LB

1tufgt · Apr 20, 2020

Agreed but that would be adding another step in the process when you can access the DB's directly from CR. So you wouldn't have to create another process to dump a file out to excel for it to consume. Thanks for the help.

lbass · Apr 20, 2020

Still thinking about this. Are you able to do ANY joins between the two databases? If you could do left joins, then you could do 1 sub with db1 with a left join to db2, and a second sub with a left join from db2 to db1. You could then pass a shared array from the first subreport to the second in order to suppress duplicates (people found in both databases). This would give you a unique set of results including all combinations. If you remove the borders around the subs, the results would appear to be from one dataset. You just wouldn't be able to sort them together.

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

1tufgt · Apr 20, 2020

That's the path that I'm going down right now is I currently have 2 sub reports. One that does enforces a left join from DB1 to DB2 and another from DB2 to DB1. So the idea you have is the path I'm working down.

Can you explain more about possibly passing a array string to the 2nd subreport to suppress the duplicates?

lbass · Apr 21, 2020

Can't get to this until tomorrow afternoon.

-LB

lbass · Apr 21, 2020

I really think that if you can link the two dbs that you should be able to use these two dbs in a command. I will work out the array formulas for you, but in the meantime, could you go into one of the subreports and then go to database->show SQL query and copy and paste the results into this thread?

Also, if there are selection criteria that don't appear in the SQL query, can you paste the selection formula here also? Thanks.

-LB

1tufgt · Apr 21, 2020

I ended up using a string to build out my unique ids that I use in the 2nd subreport to suppress those records. That solve the problem I was working on.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Outer Join Work Around?

1tufgt

Programmer

lbass

Technical User

1tufgt

Programmer

lbass

Technical User

1tufgt

Programmer

SkipVought

Programmer

lbass

Technical User

SkipVought

Programmer

1tufgt

Programmer

lbass

Technical User

1tufgt

Programmer

lbass

Technical User

1tufgt

Programmer

lbass

Technical User

lbass

Technical User

1tufgt

Programmer

Similar threads

Part and Inventory Search

Sponsor