Solved

SQL - Query to compare two tables

Posted on 2014-04-08
5
242 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 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

756 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