• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

Loop in oracle forms

have a forms procedure called fetch_dtl

 Cursor C1 is  
select distinct to_char(sort_date,'MM/DD/RRRR')as sort_date,
       to_char(sort_date,'Dy') as day,
       ds_sequential_number,
            p_sequential_number,
            dsa.social_security_no,
            SUBSTR(TO_CHAR(dsa.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(dsa.start_time, 'FM0000'),3,2)||dsa.start_ampm as schedule_start,
           SUBSTR(TO_CHAR(dsa.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(dsa.end_time, 'FM0000'),3,2)||dsa.end_ampm as time_end,      
           SUBSTR(TO_CHAR(p.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(p.start_time, 'FM0000'),3,2)||p.start_ampm as punch_time_start,
           SUBSTR(TO_CHAR(p.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(p.end_time, 'FM0000'),3,2)||p.end_ampm as punch_time_end,      
           dsa.reason_code,
           to_char(((to_date(to_char(dsa.end_time,'0000')||dsa.end_ampm || 'M' ,'HHMIAM')
       -to_date(to_char(dsa.start_time,'0000')||dsa.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90') as sched_hours,      
       pay_hours,      
            p_or_ds_id
  from v_reconciled vr,dept_staff_archive dsa,punch p
 where vr.social_security_no = dsa.social_security_no
   and vr.sort_date = dsa.schedule_date
   and vr.social_security_no = p.social_security_no(+)
   and vr.sort_date = p.start_date(+)
   and dsa.social_security_no = :blk.social_security_no
  -- and sort_date between TO_DATE(:from_date_range,'dd-mon-yyyy') and TO_DATE(:to_date_range,'dd-mon-yyyy')
  and sort_date between :from_date_range and :to_date_range
 order by sort_date;
 
BEGIN
      

Open C1;
Loop
Fetch C1 into
:blk1.sort_date,
:blk1.day,
:blk1.ds_sequential_number,
:blk1.p_sequential_number,
:blk1.social_security_no,
:blk1.schedule_start,
:blk1.time_end,
:blk1.punch_time_start,
:blk1.reason_code,
:blk1.sched_hours,
:blk1.pay_hours,
:blk1.p_or_ds_id;
exit when c1%notfound;
End loop;
close c1;
END;

After I enter deptid,start date and end date I execute this forms procedure. Get only the last record.
Any help is appreciated.
screen-print.JPG
0
anumoses
Asked:
anumoses
  • 4
  • 3
1 Solution
 
sdstuberCommented:
in your loop you replace the values of each variable.

So, it makes sense that you'd only see the last record.  You need to do something with the results on each pass through before overwriting the values on the next fetch.
0
 
sdstuberCommented:
in simpler form your loop is essentially the same as this...

:x := 0;
loop
   :x := :x + 1;
   exit when :x = 10;
end loop;

when the loop is done x = 10, you don't see 0-9 because you never do anything with those values.
0
 
anumosesAuthor Commented:
I used this
declare
Cursor C1 is
select distinct to_char(sort_date,'MM/DD/RRRR')as sort_date,
       to_char(sort_date,'Dy') as day,
       ds_sequential_number,
            p_sequential_number,
            dsa.social_security_no,
            SUBSTR(TO_CHAR(dsa.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(dsa.start_time, 'FM0000'),3,2)||dsa.start_ampm as schedule_start,
           SUBSTR(TO_CHAR(dsa.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(dsa.end_time, 'FM0000'),3,2)||dsa.end_ampm as time_end,      
           SUBSTR(TO_CHAR(p.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(p.start_time, 'FM0000'),3,2)||p.start_ampm as punch_time_start,
           SUBSTR(TO_CHAR(p.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(p.end_time, 'FM0000'),3,2)||p.end_ampm as punch_time_end,      
           dsa.reason_code,
           to_char(((to_date(to_char(dsa.end_time,'0000')||dsa.end_ampm || 'M' ,'HHMIAM')
       -to_date(to_char(dsa.start_time,'0000')||dsa.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90') as sched_hours,      
       pay_hours,      
            p_or_ds_id
  from v_reconciled vr,dept_staff_archive dsa,punch p
 where vr.social_security_no = dsa.social_security_no
   and vr.sort_date = dsa.schedule_date
   and vr.social_security_no = p.social_security_no(+)
   and vr.sort_date = p.start_date(+)
   and dsa.social_security_no = 328428824
   and sort_date between '01-jan-2010' AND '15-jan-2010'
 order by sort_date;
 
 Begin
 FOR cur_rec in C1
 LOOP
 dbms_output.put_line('sort date='||cur_rec.sort_date);
  dbms_output.put_line('day='||cur_rec.day);
   dbms_output.put_line('seq no='||cur_rec.ds_sequential_number);
    dbms_output.put_line('ssn='||cur_rec.social_security_no);
       dbms_output.put_line('start='||cur_rec.schedule_start);
        dbms_output.put_line('end='||cur_rec.time_end);
         dbms_output.put_line('punch start='||cur_rec.punch_time_start);
          dbms_output.put_line('punch end='||cur_rec.punch_time_end);
             dbms_output.put_line('rc='||cur_rec.reason_code);
              dbms_output.put_line('sched hours='||cur_rec.sched_hours);
               dbms_output.put_line('pay hours='||cur_rec.pay_hours);
End loop;
End;
--------------
Works fine
In the form I tried using this, but does not work
BEGIN


FOR cur_rec in C1
LOOP
            :blk1.sort_date := cur_rec.sort_date;
            :blk1.day := cur_rec.day;
            :blk1.ds_sequential_number := cur_rec.ds_sequential_number;
            :blk1.p_sequential_number := cur_rec.p_sequential_number;
            :blk1.social_security_no := cur_rec.social_security_no;
            :blk1.schedule_start  := cur_rec.schedule_start;
            :blk1.time_end := cur_rec.time_end;
            :blk1.punch_time_start := cur_rec.punch_time_start;
            :blk1.punch_time_end := cur_rec.punch_time_end;
            :blk1.reason_code := cur_rec.reason_code;
            :blk1.sched_hours := cur_rec.sched_hours;
                      :blk1.pay_hours := cur_rec.pay_hours;
                                :blk1.p_or_ds_id := cur_rec.p_or_ds_id;
create_record;
End loop;
first_record;
END;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
what does not work about it?  what is create_record?
0
 
anumosesAuthor Commented:
create_record- thought it will display all records, but was giving error. So removed it. Now the code only displays the last record.
0
 
sdstuberCommented:
like the dbms_output example - you need to do something with the results each time through.  if create record doesn't do what you want then either modify it or use something else
0
 
Helena Markováprogrammer-analystCommented:
Try this:

go_block('blk1');
first_record;
FOR cur_rec in C1
LOOP
            :blk1.sort_date := cur_rec.sort_date;
            :blk1.day := cur_rec.day;
            :blk1.ds_sequential_number := cur_rec.ds_sequential_number;
            :blk1.p_sequential_number := cur_rec.p_sequential_number;
            :blk1.social_security_no := cur_rec.social_security_no;
            :blk1.schedule_start  := cur_rec.schedule_start;
            :blk1.time_end := cur_rec.time_end;
            :blk1.punch_time_start := cur_rec.punch_time_start;
            :blk1.punch_time_end := cur_rec.punch_time_end;
            :blk1.reason_code := cur_rec.reason_code;
            :blk1.sched_hours := cur_rec.sched_hours;
                      :blk1.pay_hours := cur_rec.pay_hours;
                                :blk1.p_or_ds_id := cur_rec.p_or_ds_id;
next_record;
End loop;
0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now