Hi All,
We have oracle 18c on aix.
There are 2 tables 1, Table1 2.Table2_history Both have same structure
Table1 & Table2_history
---------------
sid number
timing date
name nvarchar2(10)
tid number
text clob
No primary key and index on tables. Just i need to compare both tables to get output
SQL> select s.sid,s.timing,s.name,s.tid,s.text from table1 s;
SID TIMING NAME TID TEXT
---------- --------- ---------- ---------- -----------------------------------------------------------------------------
1 13-MAY-21 steve 100 newyark
2 13-MAY-21 mark 200 parris
3 13-MAY-21 peter 300 london
4 13-MAY-21 john 400 w dc
5 13-MAY-21 marry 500 berlin
SQL> select * from table2_history;
SID TIMING NAME TID TEXT
---------- --------- ---------- ---------- -----------------------------------------------------------------------------
1 13-MAY-21 robert 100 delhi
2 13-MAY-21 tailor 200 parris
3 13-MAY-21 steve 300 newyark
4 13-MAY-21 mark 400 singapore
5 13-MAY-21 stella 500 dubai
SQL> select s.sid,s.timing,s.name,s.tid,s.text from table1 s, table2_history s1 where s.NAME=s1.name;
SID TIMING NAME TID TEXT
---------- --------- ---------- ---------- -----------------------------------------------------------------------------
1 13-MAY-21 steve 100 newyark
2 13-MAY-21 mark 200 parris
When i match two tables with equal sign, the expected output came.
Tried to get the non-matching data between two tables from the below sql .it goes to cartesian join and returns duplicate rows.
SQL> select s.sid,s.timing,s.name,s.tid,s.text from table1 s, table2_history s1 where s.NAME<>s1.name;
SID TIMING NAME TID TEXT
---------- --------- ---------- ---------- -----------------------------------------------------------------------------
1 13-MAY-21 steve 100 newyark
1 13-MAY-21 steve 100 newyark
1 13-MAY-21 steve 100 newyark
1 13-MAY-21 steve 100 newyark
2 13-MAY-21 mark 200 parris
2 13-MAY-21 mark 200 parris
2 13-MAY-21 mark 200 parris
2 13-MAY-21 mark 200 parris
3 13-MAY-21 peter 300 london
3 13-MAY-21 peter 300 london
3 13-MAY-21 peter 300 london
3 13-MAY-21 peter 300 london
3 13-MAY-21 peter 300 london
4 13-MAY-21 john 400 w dc
4 13-MAY-21 john 400 w dc
4 13-MAY-21 john 400 w dc
4 13-MAY-21 john 400 w dc
4 13-MAY-21 john 400 w dc
5 13-MAY-21 marry 500 berlin
5 13-MAY-21 marry 500 berlin
5 13-MAY-21 marry 500 berlin
5 13-MAY-21 marry 500 berlin
5 13-MAY-21 marry 500 berlin
23 rows selected.
SQL>
Tried to use distinct but not able to use due to clob datatype
SQL> select distinct s.sid,s.timing,s.name,s.tid,s.text from table1 s, table2_history s1 where s.NAME<>s1.name;
select distinct s.sid,s.timing,s.name,s.tid,s.text from table1 s, table2_history s1 where s.NAME<>s1.name
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
How to use <> operator to get unmatched data between tables
select s.sid,s.timing,s.name,s.tid,s.text from table1 s, table2_history s1 where s.NAME<>s1.name;
Please share your suggestions.
Thanks,