Solved

SQL - Query to compare two tables

Posted on 2014-04-08
5
243 Views
Last Modified: 2014-06-03
Hi,

Im running Oracle 10g and I want to compare two tables and get as a result the parameters below:

Both table have the same colums:

 DPSN_DESTINATIONADDRESS             VARCHAR2(21)
 DPSN_IMSI                                          VARCHAR2(18)

On table #1 I have this data:

DPSN_DESTINATIONADDRE DPSN_IMSI
--------------------- ------------------
5511991525342         724051000936726

And on table #2 I have this:

DPSN_DESTINATIONADDRE DPSN_IMSI
--------------------- ------------------
5511991525342         089724055100997175

Note that on table #2 I have the 089 in fornt of the DPSN_IMSI and on the table#1 I dont, so it needs to use the concat or something similar.

As you can see, some rows on the table #1 and table #2 have the DPSN_DESTINATIONADDRES equal between them.

So I need the output where the DPSN_DESTINATIONADDRESS is equal but the DPSN_IMSI is different between them.

Some rows on both tables may have the DPSN_IMSI or DPSN_DESTINATIONADDRESS null.. these values should be discarted as well...

The output in other needs to be:
DPSN_IMSI from table #1
DPSN_DESTINATIONADDRESS from table #1 or #2 (doesnt matter since they are equal)
DPSN_IMSI from table #2

NOTE: DPSN_IMSI from table #1 and DPSN_IMSI from table #2 needs to be different.. the rows wehre the DPSN_IMSI are equal between the table needs to be discarted.

Is this possible to be done?

Tks,
Joao
0
Comment
Question by:joaotelles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 39986405
I believe that this will do what you are looking for.

SELECT t1.dpsn_imsi, 
       t1.dpsn_destinationaddress, 
       t2.dpsn_imsi 
FROM   table_1 t1, 
       table_2 t2 
WHERE  t1.destination_address = t2.destination_address 
       AND '089' 
           ||t1.dpsn_imsi != t2.dpsn_imsi; 

Open in new window


Because of the equality, the nulls should be filtered out.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 39988827
select * from
(select t1.dpsn_imsi t1imsi, t1.dspn_destinationaddress, t2.dspn_imsi t2imsi
 from table1 t1, table2 t2
 where t1.dspn_destination_address = t2.dspn_destination_address)
where t1imsi <> t2imsi;
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39988883
awking00,

How does that account for the prepended 089 on records in one table?
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 500 total points
ID: 39988925
Thanks for pointing that out. The last where clause needs to be modified to
where '089'||t1imsi <> t2imsi;
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 39989118
Is 089 will always append, if not then you can use below Logic.
WHERE INSTR(t2imsi,t1imsi)>0 AND -- To make sure 2nd string present in first.
( LENGTH(t2imsi) - LENGTH(t1imsi) - INSTR(t2imsi,t1imsi) ) = 0 -- To make sure, instr is from starting
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help separating values from a column and creating a new record 6 67
scheduler notification 9 72
Estimating my database size 7 47
only show the last 365 days 6 32
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

731 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question