Oracle/sql Query Help

[MegaPowerRockstar]

Hi Guys,
Need help in getting a query for my scenario.

I have 2 table Table A and Table B. Both have same columns and data type.

Table A:
Id  PersonId    Name      Code      Date
1    1000           John         M         2014-07-28
2    1001           Mary         B         2014-07-28
3    1002           Robin        R         2014-07-28
4    1003           Maxie        N         2014-07-28

 

Table B:
Id  PersonId    Name      Code      Date
1    1000           John                     2014-07-28
2    1001           Hary         S         2014-07-28
3    1002           Justin       Q         2012-07-28
4    1003           Maxie       P         2013-07-28

My situation is:
I need to compare both the tables and need to identify the rows that has a change in all columns, if all other columns are not changed and Code is changed to blank, i need to ignore that row.

The expected outcome is:

Id  PersonId    Name      Code      Date
2    1001           Hary         S         2014-07-28
3    1002           Justin       Q         2012-07-28
4    1003           Maxie       P         2013-07-28

 

Thanks Guys...

[upendra_super]

ltt

[CheGuevara]

select * from TABLEB

  minus
  select * from TABLEA
 

[puli_keka]

join both tables on personid  and  in where condition compare other three columns

[150bryant]

select distinct * from TABLEA A , TABLEB B 

where A.ID =B.ID 

and

A.name <> B.name

or

A.code <> B.code

or

A.Date <> B.date 

[150bryant]

(select * from A  minus select * from B  )-- Rows in A that are not in B

union all

(  select * from B minus select * from A) -- rows in B that are not in A

[ChampakDas]

is it all or any?

 

Hi Guys,
Need help in getting a query for my scenario.

I have 2 table Table A and Table B. Both have same columns and data type.

Table A:
Id  PersonId    Name      Code      Date
1    1000           John         M         2014-07-28
2    1001           Mary         B         2014-07-28
3    1002           Robin        R         2014-07-28
4    1003           Maxie        N         2014-07-28

 

Table B:
Id  PersonId    Name      Code      Date
1    1000           John                     2014-07-28
2    1001           Hary         S         2014-07-28
3    1002           Justin       Q         2012-07-28
4    1003           Maxie       P         2013-07-28

My situation is:
I need to compare both the tables and need to identify the rows that has a change in all columns, if all other columns are not changed and Code is changed to blank, i need to ignore that row.

The expected outcome is:

Id  PersonId    Name      Code      Date
2    1001           Hary         S         2014-07-28
3    1002           Justin       Q         2012-07-28
4    1003           Maxie       P         2013-07-28

 

Thanks Guys...

 

 

[mtkr]

select distinct * from TABLEA A , TABLEB B
where A.ID =B.ID
and
A.name <> B.name
or
A.code <> B.code
or
A.Date <> B.date

+
where A.persionid = B.personid

[MegaPowerRockstar]

select * from TABLEB

  minus
  select * from TABLEA
 

I have already used it...but it identifies the row that has only code change as blank...which i should ignore

[LungiLingaraju]

select distinct * from TABLEA A , TABLEB B
where A.ID =B.ID
and
A.name <> B.name
or
A.code <> B.code
or
A.Date <> B.date

this will it work .. Atanu Migita columns no change and only code column change undali annadu kada

[MegaPowerRockstar]

(select * from A  minus select * from B  )-- Rows in A that are not in B

union all

(  select * from B minus select * from A) -- rows in B that are not in A

dude...this minus gives all the changes.. as mentioned.

i need to ignore the change on the code if it is changed to blank

 

and with this query it wont work......

[MegaPowerRockstar]

is it all or any?

i didnt get you....

I need to compare all columns and if only code changed from value to blank and all other columns are not changed then i need to ignore...

 

If other columns are changed and irrespective of blank or value in the code i need to identify

[mtkr]

create table TABLEA
(
id varchar(20),
personid varchar(20),
name varchar(20),
code varchar(20)
)


create table TABLEB
(
id varchar(20),
personid varchar(20),
name varchar(20),
code varchar(20)
)

insert into tableA values ('1','1000','John','M')
insert into tableA values ('2','1001','Mary','B');
insert into tableA values ('3','1002','Robin','R');
insert into tableA values ('4','1003','Maxie','N');
insert into tableA values ('5','1004','Dave','A');

insert into tableB values ('1','1000','John','');
insert into tableB values ('2','1001','Hary','S');
insert into tableB values ('3','1002','Justin','Q');
insert into tableB values ('4','1003','Maxie','P');
insert into tableB values ('5','1004','Dave','A');


select distinct B.*
from tablea A inner join tableb B
on 
A.ID = B.ID
and a.personid = b.personid  
and NVL (B.code, 't') <> 't'
and (A.name <> B.name or  NVL (A.code, 't') <> NVL (B.code, 't'))


Result:
ID    PERSONID  NAME   CODE
2	1001	Hary  	S
3	1002	Justin	Q
4	1003	Maxie 	P

[MegaPowerRockstar]

create table TABLEA(id varchar(20),personid varchar(20),name varchar(20),code varchar(20))create table TABLEB(id varchar(20),personid varchar(20),name varchar(20),code varchar(20))insert into tableA values ('1','1000','John','M')insert into tableA values ('2','1001','Mary','B');insert into tableA values ('3','1002','Robin','R');insert into tableA values ('4','1003','Maxie','N');insert into tableA values ('5','1004','Dave','A');insert into tableB values ('1','1000','John','');insert into tableB values ('2','1001','Hary','S');insert into tableB values ('3','1002','Justin','Q');insert into tableB values ('4','1003','Maxie','P');insert into tableB values ('5','1004','Dave','A');select distinct B.*from tablea A inner join tableb Bon A.ID = B.IDand a.personid = b.personid  and NVL (B.code, 't') <> 't'and (A.name <> B.name or  NVL (A.code, 't') <> NVL (B.code, 't'))Result:ID    PERSONID  NAME   CODE2	1001	Hary  	S3	1002	Justin	Q4	1003	Maxie 	P

Bhayaa,...if I have 30 columns then I have to compare all 30 ?

[sk789]

FROM WHAT I UNDERSTAND, I THINK THIS WOULD WORK, IF YOU CONSIDER THE CHANGES FROM TABLE B ONLY


SELECT * FROM TABLE B WHERE ID IN
(
-------THIS QUERY CHECKS FOR THE CHANGES IN THE COLUMN APART FROM 'CODE' COLUMN
SELECT ID FROM
(
SELECT ID, PERSONID,NAME,DATE FROM TABLE B
MINUS
SELECT ID,PERSONID,NAME, DATE FROM TABLE A
)H

UNION

-------THIS QUERY CHECKS FOR THE CHANGES ONLY IN THE 'CODE' COLUMN FROM TABLE B WHEN CODE IS NOT BLANK
SELECT ID FROM
(
SELECT ID, CODE FROM TABLE B
WHERE CODE <> ' '
MINUS
SELECT ID, CODE FROM TABLE A
)K
)