• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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
0
joaotelles
Asked:
joaotelles
  • 2
  • 2
2 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
awking00Commented:
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
 
johnsoneSenior Oracle DBACommented:
awking00,

How does that account for the prepended 089 on records in one table?
0
 
awking00Commented:
Thanks for pointing that out. The last where clause needs to be modified to
where '089'||t1imsi <> t2imsi;
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now