Solved

SQL - Query to compare two tables

Posted on 2014-04-08
5
240 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
  • 2
  • 2
5 Comments
 
LVL 34

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now