OraFAQ Forum: SQL & PL/SQL » Complex comparision query

Home » SQL & PL/SQL » SQL & PL/SQL » Complex comparision query (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Complex comparision query [message #634443]

Tue, 10 March 2015 03:25

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

I have two tables one containing old data or original values and the second table containing modified values , i need to make a comparision report like what was original and what changes has been done.


create table rev_1 (old_pm_code varchar2(12),old_ps_code varchar2(12),old_ps_qty number );
 
 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100101',null,10);
 
 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100101','01',5);
 
 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100101','02',5);

select * from rev_1

OLD_PM_CODE,OLD_PS_CODE,OLD_PS_QTY
100101                   10
100101       01           5
100101       02           5

create table rev_2 (new_pm_code varchar2(12),new_ps_code varchar2(12),new_ps_qty number );
  
  insert into rev_2(new_pm_code,new_ps_code,new_ps_qty) values ('100101',null,10);
  
  insert into rev_2(new_pm_code,new_ps_code,new_ps_qty) values ('100101','01',4);
  
  insert into rev_2(new_pm_code,new_ps_code,new_ps_qty) values ('100101','02',6);


NEW_PM_CODE,NEW_PS_CODE,NEW_PS_QTY
100101                     10
100101         01           4
100101         02           6


my desired output is


old_pm_code,  new_pm_code , old_ps_code , new_ps_code , old_qty , new_qty , remarks
100101          100101                                   10         10      no change
100101          100101         01             01          5          4      qty decrease
100101          100101         02             02          5          6      qty increase

Lalit : Removed superfluous lines in the end

[Updated on: Tue, 10 March 2015 05:44] by Moderator

Report message to a moderator

Re: Complex comparision query [message #634446 is a reply to message #634443]

Tue, 10 March 2015 03:31

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How do you relate the rows of first table to the rows of second one?
What is the join condition?

Report message to a moderator

Re: Complex comparision query [message #634459 is a reply to message #634446]

Tue, 10 March 2015 05:24

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

thanks michael , actually i managed to do it using join but the real problem is if there are different inserts in two different tables.For example


SELECT old_pm_code, old_ps_code, old_ps_qty, new_pm_code, new_ps_code,
       new_ps_qty
  FROM rev_1, rev_2
 WHERE old_pm_code = new_pm_code
   AND NVL (old_ps_code, 'x') = NVL (new_ps_code, 'x')


OLD_PM_CODE,OLD_PS_CODE,OLD_PS_QTY,NEW_PM_CODE,NEW_PS_CODE,NEW_PS_QTY
100101             10                100101                   10
100101             01       5        100101       01           4
100101             02       5        100101       02           6


-- now in case if i want to add one record in rev_2 and another in rev_1

 insert into rev_2(new_pm_code,new_ps_code,new_ps_qty) values ('100101','03',6);

 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100101','04',5)


-- i used the following full outer join,the output as below , is there a better way to do this and make it more readable like

OLD_PM_CODE,OLD_PS_CODE,NEW_PM_CODE,NEW_PS_CODE
100101                    100101
100101         01         100101      01
100101         02         100101      02
100101         04          
                          100101      03



--- i want it as below
OLD_PM_CODE,OLD_PS_CODE,NEW_PM_CODE,NEW_PS_CODE
100101                    100101
100101         01         100101      01
100101         02         100101      02
100101         04         100101 
100101                    100101      03

Lalit : removed superfluous lines in the end

[Updated on: Tue, 10 March 2015 05:43] by Moderator

Report message to a moderator

Re: Complex comparision query [message #634464 is a reply to message #634459]

Tue, 10 March 2015 06:25

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

--- i want it as below

For what I can see, just use "NVL(old_pm_code,new_pm_code)" on both columns.

Report message to a moderator

Re: Complex comparision query [message #634472 is a reply to message #634464]

Tue, 10 March 2015 07:03

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

thanks Michael, it worked.


SELECT NVL (old_pm_code, new_pm_code) old_pm_code, old_ps_code,
       NVL (old_pm_code, new_pm_code) new_pm_code, new_ps_code
  FROM rev_1 FULL OUTER JOIN rev_2
       ON NVL (old_pm_code, 'x') = NVL (new_pm_code, 'x')
     AND NVL (old_ps_code, 'x') = NVL (new_ps_code, 'x')

Report message to a moderator

Re: Complex comparision query [message #634475 is a reply to message #634472]

Tue, 10 March 2015 07:21

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Now, how does it come for your first "my desired output is"?
Or is this no more the question?

Report message to a moderator

Re: Complex comparision query [message #634504 is a reply to message #634475]

Wed, 11 March 2015 00:08

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Quote:

Now, how does it come for your first "my desired output is"?
Or is this no more the question?

yes there is a question
, there is a problem while filtering the records using where condition on full outer join.How can it be handled in a better way than what i am doing it as below.


-- i am adding further records to both the tables.

 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100201','01',5)
 
 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100201','02',5)
 
 insert into rev_1(old_pm_code,old_ps_code,old_ps_qty) values ('100201','03',5)


SELECT NVL (old_pm_code, new_pm_code) old_pm_code, old_ps_code,
       NVL (old_pm_code, new_pm_code) new_pm_code, new_ps_code
  FROM rev_1 FULL OUTER JOIN rev_2
       ON (    NVL (old_pm_code, 'x') = NVL (new_pm_code, 'x')
           AND NVL (old_ps_code, 'x') = NVL (new_ps_code, 'x')
          )
 WHERE old_pm_code = '100101'

--if i run the above query record from rev_2 table with new_ps_code '03' is missing 

OLD_PM_CODE,OLD_PS_CODE,NEW_PM_CODE,NEW_PS_CODE
100101                    100101
100101         01         100101      01
100101         02         100101      02
100101         04         100101

--if i filter it with using new_pm_code then '03' is comming but recently added data in rev_1 is missing.

SELECT NVL (old_pm_code, new_pm_code) old_pm_code, old_ps_code,
       NVL (old_pm_code, new_pm_code) new_pm_code, new_ps_code
  FROM rev_1 FULL OUTER JOIN rev_2
       ON (    NVL (old_pm_code, 'x') = NVL (new_pm_code, 'x')
           AND NVL (old_ps_code, 'x') = NVL (new_ps_code, 'x')
          )
 WHERE new_pm_code = '100101'

--result the old_ps_code '04' is missing.

OLD_PM_CODE,OLD_PS_CODE,NEW_PM_CODE,NEW_PS_CODE
100101                     100101
100101        01           100101      01
100101        02           100101      02
100101                     100101      03

now i am using nvl(old_pm_code,new_pm_code) to bring all the records , is this approach correct ?

SELECT   NVL (old_pm_code, new_pm_code) old_pm_code, old_ps_code,
         NVL (old_pm_code, new_pm_code) new_pm_code, new_ps_code
    FROM rev_1 FULL OUTER JOIN rev_2
         ON (    NVL (old_pm_code, 'x') = NVL (new_pm_code, 'x')
             AND NVL (old_ps_code, 'x') = NVL (new_ps_code, 'x')
            )
   WHERE NVL (old_pm_code, new_pm_code) = '100101'
ORDER BY 1, 4     


--results

OLD_PM_CODE,OLD_PS_CODE,NEW_PM_CODE,NEW_PS_CODE
100101         01          100101      01
100101         02          100101      02
100101                     100101      03
100101         04          100101
100101                     100101

Report message to a moderator

Previous Topic:	How to remove the NULL values from SELECT clause
Next Topic:	cursor and loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 23:41:26 CDT 2024