SeanO
11-29-2005, 05:43 PM
Trying to figure out how to peform an outer join on two identical tables. All information I can find (this is a new one for me) relates to different tables with the same key, and I have not yet been able to actually get any of these examples to work.
What I've got:
This is a new vendor software version that we are testing. Our DBA has created a link, which she called newtoprod, between the new version db and a production version db. Starting from the new version db, she has instructed me to refer to the production version in this manner - tablename@linkname. The tables, each called z11, each contain doc_number and rec_key. In the new version of this db, there are 40 more records where the rec_key like '020%'. The rec_key in the new version of the db is not always the same as in the production version, so I need to use doc_number as my key. To use the link my dba created, I am performing my sql query from the new version and referring to the production table as z11@newtoprod
What I need:
In an effort to debug this problem, the project manager wants to see only those doc_numbers in the new version of the db that are not in the production version.
Any ideas where to at least start on this? I've tried various outer join approaches but I routinely end up with ambiguously defined fields, so I am sure I'm doing something wrong! If anyone knows of examples of outer joins on identical tables, this would at least be a more helpful than what I've been able to find thus far.
Thanks to all in advance for any assistance.
Seán O.
What I've got:
This is a new vendor software version that we are testing. Our DBA has created a link, which she called newtoprod, between the new version db and a production version db. Starting from the new version db, she has instructed me to refer to the production version in this manner - tablename@linkname. The tables, each called z11, each contain doc_number and rec_key. In the new version of this db, there are 40 more records where the rec_key like '020%'. The rec_key in the new version of the db is not always the same as in the production version, so I need to use doc_number as my key. To use the link my dba created, I am performing my sql query from the new version and referring to the production table as z11@newtoprod
What I need:
In an effort to debug this problem, the project manager wants to see only those doc_numbers in the new version of the db that are not in the production version.
Any ideas where to at least start on this? I've tried various outer join approaches but I routinely end up with ambiguously defined fields, so I am sure I'm doing something wrong! If anyone knows of examples of outer joins on identical tables, this would at least be a more helpful than what I've been able to find thus far.
Thanks to all in advance for any assistance.
Seán O.