Home » SQL & PL/SQL » SQL & PL/SQL » Compare records using cursor
Compare records using cursor [message #411836] Tue, 07 July 2009 02:08 Go to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member
Hi,

I am a beginner in learning Oracle PLSQL.
In my Cursor SQL statement, I give

select * from emp
order by dept_no,sal
Consider for each department there are 2 records in the table.

I want to compare records in the same table using cursors. If first two records match then print the comment column in each of the record as matching. Else Print not matching.

Likewise match the next two records.

Can comeone please help me with this.....

Thanks.
Re: Compare records using cursor [message #411842 is a reply to message #411836] Tue, 07 July 2009 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you can't do it in a simple SQL statement?

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Also always post your Oracle version (4 decimals).

Before posting any code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Compare records using cursor [message #412165 is a reply to message #411836] Wed, 08 July 2009 01:41 Go to previous messageGo to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member
The requirement has changed now.
The very first record for each combination retrived by the select statement should have Indicator column value updated to "Initial". If there is a change in the next record(as per conditions in the If statement), Indicator column should be updated to "Change". If there is no change, leave the indicator field blank.

I am using the below cursor to achieve this but unable to get the desired result. Please help....

declare

prev_st char(3):=' ';
prev_lob char(3):=' ';
prev_issue char(3):=' ';
prev_term char(3):=' ';
prev_paybycd char(3):=' ';
prev_modecd char(3):=' ';
prev_tier char(3):=' ';
prev_down_per char(10):=' ';
prev_ins_cnt number(10):=0;
prev_ser_fee number(10):=0;
prev_mco char(3):=' ';
prev_pco char(3):=' ';
prev_nb_effdt date:='31-dec-9999';
prev_rnw_effdt date:='31-dec-9999';
loop_count number(5);

CURSOR Compare_records_CUR is
SELECT st,lob_cd,pol_issue_cd,term,paybycd,mode_cd,tier,down_per,ins_cnt,ser_fee,bsp_nb_effdt,bsp_rnw_effdt,mco,pco
FROM im802258
ORDER BY st,lob_cd,pol_issue_cd,term,paybycd,mode_cd,tier,mco,pco,bsp_nb_effdt,bsp_rnw_effdt;

begin
FOR j IN Compare_records_CUR

LOOP

loop_count := loop_count + 1;

if (( (prev_st =j.st and prev_lob =j.lob_cd and prev_issue=j.pol_issue_cd and prev_term=j.term
and prev_paybycd=j.paybycd and prev_modecd=j.mode_cd and prev_tier=j.tier and prev_mco=j.mco and prev_pco=j.pco and prev_nb_effdt <> j.bsp_nb_effdt and prev_rnw_effdt <> j.bsp_rnw_effdt))
and (prev_down_per <> j.down_per or prev_ins_cnt <> j.ins_cnt or prev_ser_fee <> j.ser_fee))
then
update im802258 set indicators='Change';
else
update im802258 set indicators='No Change';

end if;
prev_st := j.st;
prev_lob :=j.lob_cd;
prev_issue :=j.pol_issue_cd;
prev_term :=j.term;
prev_paybycd :=j.paybycd;
prev_modecd :=j.mode_cd;
prev_tier :=j.tier;
prev_down_per :=j.down_per;
prev_ins_cnt :=j.ins_cnt;
prev_ser_fee :=j.ser_fee;
prev_mco :=j.mco;
prev_pco :=j.pco;
prev_nb_effdt :=j.bsp_nb_effdt;
prev_rnw_effdt :=j.bsp_rnw_effdt;

END LOOP;
end;
/
Re: Compare records using cursor [message #412174 is a reply to message #412165] Wed, 08 July 2009 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help....

Please format....

Regards
Michel
Re: Compare records using cursor [message #412178 is a reply to message #411836] Wed, 08 July 2009 02:07 Go to previous messageGo to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member
Formatted Code:
SET termout on

DECLARE
   prev_st          CHAR (3)    := ' ';
   prev_lob         CHAR (3)    := ' ';
   prev_issue       CHAR (3)    := ' ';
   prev_term        CHAR (3)    := ' ';
   prev_paybycd     CHAR (3)    := ' ';
   prev_modecd      CHAR (3)    := ' ';
   prev_tier        CHAR (3)    := ' ';
   prev_down_per    CHAR (10)   := ' ';
   prev_ins_cnt     NUMBER (10) := 0;
   prev_ser_fee     NUMBER (10) := 0;
   prev_mco         CHAR (3)    := ' ';
   prev_pco         CHAR (3)    := ' ';
   prev_nb_effdt    DATE        := '31-dec-9999';
   prev_rnw_effdt   DATE        := '31-dec-9999';
   loop_count       NUMBER (5);

   CURSOR compare_records_cur
   IS
      SELECT   st, lob_cd, pol_issue_cd, term, paybycd, mode_cd, tier,
               down_per, ins_cnt, ser_fee, bsp_nb_effdt, bsp_rnw_effdt, mco,
               pco
          FROM im802258
      ORDER BY st,
               lob_cd,
               pol_issue_cd,
               term,
               paybycd,
               mode_cd,
               tier,
               mco,
               pco,
               bsp_nb_effdt,
               bsp_rnw_effdt;
BEGIN
   FOR j IN compare_records_cur
   LOOP
      loop_count := loop_count + 1;

      IF (    ((    prev_st = j.st
                AND prev_lob = j.lob_cd
                AND prev_issue = j.pol_issue_cd
                AND prev_term = j.term
                AND prev_paybycd = j.paybycd
                AND prev_modecd = j.mode_cd
                AND prev_tier = j.tier
                AND prev_mco = j.mco
                AND prev_pco = j.pco
                AND prev_nb_effdt <> j.bsp_nb_effdt
                AND prev_rnw_effdt <> j.bsp_rnw_effdt
               )
              )
          AND (   prev_down_per <> j.down_per
               OR prev_ins_cnt <> j.ins_cnt
               OR prev_ser_fee <> j.ser_fee
              )
         )
      THEN
         UPDATE im802258
            SET indicators = 'Change';
      ELSE
         UPDATE im802258
            SET indicators = 'No Change';
      END IF;

      prev_st := j.st;
      prev_lob := j.lob_cd;
      prev_issue := j.pol_issue_cd;
      prev_term := j.term;
      prev_paybycd := j.paybycd;
      prev_modecd := j.mode_cd;
      prev_tier := j.tier;
      prev_down_per := j.down_per;
      prev_ins_cnt := j.ins_cnt;
      prev_ser_fee := j.ser_fee;
      prev_mco := j.mco;
      prev_pco := j.pco;
      prev_nb_effdt := j.bsp_nb_effdt;
      prev_rnw_effdt := j.bsp_rnw_effdt;
   END LOOP;
END;
/

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 08 July 2009 03:12] by Moderator

Report message to a moderator

Re: Compare records using cursor [message #412183 is a reply to message #412178] Wed, 08 July 2009 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 07 July 2009 09:29
Are you sure you can't do it in a simple SQL statement?

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Also always post your Oracle version (4 decimals).

Before posting any code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.


Regards
Michel


Re: Compare records using cursor [message #412187 is a reply to message #412178] Wed, 08 July 2009 03:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Formatted part of your code:
preethi 12$ wrote on Wed, 08 July 2009 09:07
      IF <some condition>
      THEN
         UPDATE im802258
            SET indicators = 'Change';
      ELSE
         UPDATE im802258
            SET indicators = 'No Change';
      END IF;


As UPDATE statement does not contain any WHERE condition, it updates all rows in IM802258 in every cursor LOOP.
If you want to update only current row in cursor, use e.g. CURRENT OF in the WHERE clause. It is described in PL/SQL User's Guide and Reference, available with many other Oracle documnetation books e.g. online on http://tahiti.oracle.com/.

Also 'unable to get the desired result' problem description is pretty vague, as you did not specify, what 'the desired result' is. Please, be more specific in your posts.
Re: Compare records using cursor [message #412190 is a reply to message #412178] Wed, 08 July 2009 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, you never set the first record's value to "Initial" (as the requirement says).



prev_nb_effdt    DATE        := '31-dec-9999';
is wrong - you should not do that (putting a string into a DATE datatype column). True, it looks like a date (to you and me), but it is a string. On my database, it would produce an error. Always use TO_DATE function:
prev_nb_effdt    DATE        := to_date('31-dec-9999', 'dd-mon-yyyy');
Re: Compare records using cursor [message #412191 is a reply to message #412178] Wed, 08 July 2009 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Your update statements are wrong - they will both update the whole table rather than just a few lines

2) You say that there are two records for each department. You step through each record in the cursor, and compare al the fields with the previous record - every time you change department, all the fields won't match, so the first record of each pair would be marked as different.

I'd do it like this:
create table test_001 (col_1 number,col_2 number, col_3 number);

insert into test_001 values (1,2,3);
insert into test_001 values (1,2,3);
insert into test_001 values (2,6,6);
insert into test_001 values (2,6,7);

select col_1
from  (select distinct col_1,col_2,col_3
       from   test_001)
group by col_1
having count(*) > 1;


Re: Compare records using cursor [message #412262 is a reply to message #411836] Wed, 08 July 2009 07:22 Go to previous messageGo to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member
Thanks for the response...

I am modified the code such that it updates only as 'Change' and 'No Change' for records whose value is not equal to 'Initial'. I have updated in the table manually by updating 'Initial' as value for Indicator Column for the very first occurance. Only the nect occurances should be updated as 'Change' or 'No Change'.

But when I run the code, I can only see the value 'Initial' which is updated by me manually. The code is not updating the table at all.

-----------------------------
set termout on

declare

prev_st char(3):=' ';
prev_lob char(3):=' ';
prev_issue char(3):=' ';
prev_term char(3):=' ';
prev_paybycd char(3):=' ';
prev_modecd char(3):=' ';
prev_tier char(3):=' ';
prev_down_per char(10):=' ';
prev_ins_cnt number(10):=0;
prev_ser_fee number(10):=0;
prev_mco char(3):=' ';
prev_pco char(3):=' ';
temp number(2);

CURSOR Compare_records_CUR is
SELECT st,lob_cd,pol_issue_cd,term,paybycd,mode_cd,tier,down_per,ins_cnt,ser_fee,bsp_nb_effdt,bsp_rnw_effdt,mco,pco
FROM im802258
ORDER BY st,lob_cd,pol_issue_cd,term,paybycd,mode_cd,tier,mco,pco,bsp_nb_effdt,bsp_rnw_effdt;

BEGIN
FOR j IN Compare_records_CUR

LOOP

IF (prev_st =j.st AND prev_lob =j.lob_cd AND prev_issue=j.pol_issue_cd AND prev_term=j.term AND
prev_paybycd=j.paybycd AND prev_modecd=j.mode_cd AND prev_tier=j.tier AND prev_mco=j.mco AND
prev_pco=j.pco) THEN

IF(prev_down_per <> j.down_per OR prev_ins_cnt <> j.ins_cnt OR prev_ser_fee <> j.ser_fee) THEN

UPDATE im802258 SET indicators = 'Change'
WHERE st = j.st
AND lob_cd = j.lob_cd
AND pol_issue_cd = j.pol_issue_cd
AND prev_term = j.term
paybycd = j.paybycd
AND mode_cd = j.mode_cd
AND tier = j.tier
AND mco = j.mco
AND pco = j.pco
AND indicators <> 'Initial';
COMMIT;
ELSE
UPDATE im802258 SET indicators = 'No Change'
WHERE st = j.st
AND lob_cd = j.lob_cd
AND pol_issue_cd = j.pol_issue_cd
AND prev_term = j.term
paybycd = j.paybycd
AND mode_cd = j.mode_cd
AND tier = j.tier
AND mco = j.mco
AND pco = j.pco
AND indicators <> 'Initial';
COMMIT;
END IF;

ELSIF (prev_st != j.st OR prev_lob !=j.lob_cd OR prev_issue !=j.pol_issue_cd OR prev_term !=j.term OR
prev_paybycd !=j.paybycd OR prev_modecd !=j.mode_cd OR prev_tier != j.tier OR prev_mco !=j.mco OR prev_pco !=j.pco) THEN
temp:=NULL;


prev_st := j.st;
prev_lob :=j.lob_cd;
prev_issue :=j.pol_issue_cd;
prev_term :=j.term;
prev_paybycd :=j.paybycd;
prev_modecd :=j.mode_cd;
prev_tier :=j.tier;
prev_down_per :=j.down_per;
prev_ins_cnt :=j.ins_cnt;
prev_ser_fee :=j.ser_fee;
prev_mco :=j.mco;
prev_pco :=j.pco;

END IF;
END LOOP;


END;
/



Re: Compare records using cursor [message #412272 is a reply to message #412262] Wed, 08 July 2009 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 08 July 2009 09:47
Michel Cadot wrote on Tue, 07 July 2009 09:29
Are you sure you can't do it in a simple SQL statement?

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Also always post your Oracle version (4 decimals).

Before posting any code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.


Regards
Michel




Re: Compare records using cursor [message #412277 is a reply to message #412262] Wed, 08 July 2009 07:53 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Well, then I would assume that you did something wrong when you updated "the first row" to 'Initial'. Then in your update statements you have "AND indicators <> 'Initial'", so that none of the entries you updated to initial will be updated. Did you try selecting from your table after updating to 'Initial' to see the new values?
Re: Compare records using cursor [message #412285 is a reply to message #412262] Wed, 08 July 2009 08:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Look at your logic:
IF (prev_st =j.st AND prev_lob =j.lob_cd AND prev_issue=j.pol_issue_cd AND prev_term=j.term AND
prev_paybycd=j.paybycd AND prev_modecd=j.mode_cd AND prev_tier=j.tier AND prev_mco=j.mco AND
prev_pco=j.pco) THEN

IF(prev_down_per <> j.down_per OR prev_ins_cnt <> j.ins_cnt OR prev_ser_fee <> j.ser_fee) THEN

<do an update>
ELSE
<do an update>
END IF;

ELSIF


It's quite possible to go through this logic without doing an update - I suspect that this is what's happening.
Re: Compare records using cursor [message #412386 is a reply to message #411836] Thu, 09 July 2009 02:06 Go to previous messageGo to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member
Hi,

Thanks everyone.. The problem was with IF THEN ELSE LOGIC.

Below logic is working.

ELSE IF (prev_st != j.st OR prev_lob !=j.lob_cd OR
prev_issue !=j.pol_issue_cd OR prev_term !=j.term OR
prev_paybycd !=j.paybycd OR prev_modecd !=j.mode_cd OR prev_tier != j.tier
OR prev_mco !=j.mco OR prev_pco !=j.pco) THEN

loop_count:=loop_count +1;

END IF;



Re: Compare records using cursor [message #412396 is a reply to message #412386] Thu, 09 July 2009 02:57 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 08 July 2009 14:41
Michel Cadot wrote on Wed, 08 July 2009 09:47
Michel Cadot wrote on Tue, 07 July 2009 09:29
Are you sure you can't do it in a simple SQL statement?

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Also always post your Oracle version (4 decimals).

Before posting any code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.


Regards
Michel






Previous Topic: Table Conversion
Next Topic: Query
Goto Forum:
  


Current Time: Fri Dec 09 15:32:32 CST 2016

Total time taken to generate the page: 0.24464 seconds