variable increment

• • Reply

  17 years ago

  by riaz1

  riaz1

  , United States

  Joined 17 years ago

  using PL/SQL
  

  
  I am currently getting few columns from two tables in desc order by one field(cgpa) . Then I increment a variable(myvar)  value by 1. But the problem is when cgpa of more than one student is same, then the variable (myvar) should not be incremented. How this is possible? Plz elp me.
  

  
  my code is as below:
  

  
  create or replace procedure meritno as
  cursor c1 is select rn,faculty,regno,round(cgpa,2) a from grn,it
  where grn.rn=it.regno and
  it.sc=2 and it.sy=2003
  and grn.faculty='CS'
  order by it.cgpa desc;
  rec1 c1%rowtype;
  myvar number:=1;
  begin
  for x in c1 loop
     dbmsoutput.putline(myvar||' '||x.regno||' '||x.a);
   myvar:= my_var+1;
  end loop;
  end;

  Report abuse
• • Reply

  17 years ago

  by lavanya

  Lavanya Arul

  , Malaysia

  Joined 17 years ago

  Hi,
  You can have a variable, which will store the previous value.
  

  
  create or replace procedure meritno as
  cursor c1 is select rn,faculty,regno,round(cgpa,2) a from grn,it
  where grn.rn=it.regno and
  it.sc=2 and it.sy=2003
  and grn.faculty='CS'
  order by it.cgpa desc;
  rec1 c1%rowtype;
  myvar number:=1;
  temp varchar2(10)='';
  begin
  for x in c1 loop
    dbmsoutput.putline(myvar||' '||x.regno||' '||x.a);
   if temp <> x.cgpa then
     myvar:= my_var+1;
   end if;
   temp := x.cgpa;
  end loop;
  end;
  

  
  Chio!!
  Lavy
  

  Report abuse