1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Comparison of records in a table using cursors

Discussion in 'SQL PL/SQL' started by lakshmi_9078, Jul 7, 2009.

  1. lakshmi_9078

    lakshmi_9078 Guest

    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.
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Not sure if i understand your problem. Are you looking for duplicate records ?
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or

    Are you looking for a solution something like.. .

    Code (SQL):

    SQL> SELECT DEPTNO ,
      2         NEXT_DEPTNO ,
      3         DECODE(DEPTNO,NEXT_DEPTNO,'MATCHING','NOT MATCHING')  "COMMENT"
      4    FROM (SELECT DEPTNO ,
      5                 LEAD(DEPTNO) OVER (ORDER BY DEPTNO) AS NEXT_DEPTNO
      6            FROM EMP
      7           ORDER BY DEPTNO);

        DEPTNO NEXT_DEPTNO COMMENT
    ---------- ----------- ------------
            10          10 MATCHING
            10          10 MATCHING
            10          20 NOT MATCHING
            20          20 MATCHING
            20          20 MATCHING
            20          20 MATCHING
            20          20 MATCHING
            20          30 NOT MATCHING
            30          30 MATCHING
            30          30 MATCHING
            30          30 MATCHING
            30          30 MATCHING
            30          30 MATCHING
            30          50 NOT MATCHING
            50             NOT MATCHING

    15 ROWS selected.

    SQL>