Oracle/sql Query Help

[pachimirchi]

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

yes my boy .. ade kada nee requirement, compare all columns and get the deltas.. for the sake of performance consider using hashbytes ..

[MegaPowerRockstar]

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
)

Thanks.....

[mtkr]

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

ya bayya changes capture cheyyali kaabatti anni cols include cheyyali...

am nt sure wthr thr my be any othr short soln..

help...

[sk789]

One more addition : we did not consider the situation - if all the remaining columns were same and code column is blank in both the tables . So Use the below code.

SELECT * FROM TABLE B WHERE ID IN
(

SELECT ID FROM
(
SELECT ID, PERSONID,NAME,DATE FROM TABLE B
MINUS
SELECT ID,PERSONID,NAME, DATE FROM TABLE A
)H

UNION


SELECT ID FROM
(
SELECT ID, CODE FROM TABLE B
WHERE CODE <> ' '
MINUS
SELECT ID, CODE FROM TABLE A
)K

UNION

SELECT ID FROM
(
SELECT ID, CODE FROM TABLE B
WHERE CODE = ' '

INTERSECT

SELECT ID, CODE FROM TABLE A
WHERE CODE = ' '
)L
)

[MegaPowerRockstar]

One more addition : we did not consider the situation - if all the remaining columns were same and code column is blank in both the tables . So Use the below code.

SELECT * FROM TABLE B WHERE ID IN
(

SELECT ID FROM
(
SELECT ID, PERSONID,NAME,DATE FROM TABLE B
MINUS
SELECT ID,PERSONID,NAME, DATE FROM TABLE A
)H

UNION


SELECT ID FROM
(
SELECT ID, CODE FROM TABLE B
WHERE CODE <> ' '
MINUS
SELECT ID, CODE FROM TABLE A
)K

UNION

SELECT ID FROM
(
SELECT ID, CODE FROM TABLE B
WHERE CODE = ' '

INTERSECT

SELECT ID, CODE FROM TABLE A
WHERE CODE = ' '
)L
)

i dont need the row that has all columns same and the code being null in table b....and some value in table a....

so i can skip last select statement right...

[150bryant]

pls post your solution too...it will be helpful

 

[MegaPowerRockstar]

Thanks for all feedbacks..... i solved it myself with simple query....

it seems working for now....need to do some more testing...

 

check the sol and tell me if it is not apt one.....

 

select * from 

(

SELECT ID, PERSONID,NAME,

case when code is null then

    select a.code from tablea A where A.ID = B.ID

else

 a.code

end as Code,

DATE FROM TABLE B

MINUS
SELECT ID,PERSONID,NAME,CODE,DATE FROM TABLE A

 

)

[Johny]

hmm..gp...