×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Outer Join Work Around?

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.

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

(OP)
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

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

You can do outer joins in Excel if necessary.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

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

-LB

RE: Outer Join Work Around?

(OP)
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.

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

(OP)
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?

RE: Outer Join Work Around?

Can't get to this until tomorrow afternoon.

-LB

RE: Outer Join Work Around?

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

RE: Outer Join Work Around?

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close