asked on

SQL - Query to compare two tables

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

johnsone

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.

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

awking00,

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

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Devinder Singh Virdi

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