Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Unanswered: Cursor - for Loop problems

    I'm having problems with a report using cursors and for loops. I'm using one cursor (seljjobno) to select all required job numbers. The other cursor(selallrecs) uses the results from seljjobno as part of its query. (The jjobno from seljjobno, populates v_jjobno). These are shown below:

    CURSOR seljjobno IS
    SELECT DISTINCT r.jjobno
    FROM cb_response r, cb_jobdata j, cb_items c
    WHERE r.serial = c.serial
    AND r.jjobno = j.jjobno
    AND r.jobseq = j.jobseq
    AND j.jclcode IN ('HR', 'HB', 'HS')
    and j.cb_date >='1-MAR-03';


    CURSOR selallrecs IS
    SELECT SUM(decode(r.resp, 'Y', 1, 0)) YES,
    SUM(decode(r.resp, 'N', 1, 0)) NO,
    SUM(decode(r.resp, NULL, 1, 0)) NA,
    COUNT(r.resp) PHONED,
    r.serial,
    c.cb_text
    FROM cb_response r, cb_jobdata j, cb_items c
    WHERE r.serial = c.serial
    AND r.jjobno = j.jjobno
    AND r.jobseq = j.jobseq
    AND j.jclcode IN ('HR', 'HB', 'HS')
    AND r.jjobno = v_jjobno
    GROUP BY r.serial, c.cb_text
    ORDER BY r.serial;

    It's at this point I'm stuck. When I output the data, its broken down by jjobno which I don't need, it needs to be broken down by another field. So I need to find a way to select all the jjobno's then loop through it to populate v_jjobno without the output showing jjobno or being grouped and arranged by the jjobno. Here is the for loops:

    for every_jjobno in seljjobno LOOP

    v_jjobno := every_jjobno.jjobno;

    for every_rec in selallrecs LOOP

    v_yes := every_rec.YES;
    v_no := every_rec.NO;
    v_na := every_rec.NA;
    v_phoned := every_rec.PHONED;
    v_serial := every_rec.serial;
    v_cb_text := every_rec.cb_text;

    thanks,
    Ian

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    open seljjobno;
      
    fetch seljjobno into v_jjobno;
      while 
    seljjobno%found loop

       
    for every_rec in selallrecs loop 

        v_yes 
    := every_rec.YES;
        
    v_no := every_rec.NO;
        
    v_na := every_rec.NA;
        
    v_phoned := every_rec.PHONED;
        
    v_serial := every_rec.serial;
        
    v_cb_text := every_rec.cb_text;

      
    end loop;

    fetch seljjobno into v_jjobno;
    close seljjobno;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2003
    Posts
    20
    This works fine thanks but I've realised its not what I actually intended to do!

    When I run the second cursor because I have the v_jjobno in the filter criteria it selects and displays the information one set of records at a time, each time the v_jjobno field changes. So in effect it is still broken down by v_jjobno. What I want to do is to filter out the records using v_jjobno but show the results as a running total. So everytime the v_jjobno changes and there's valid data it adds it to a total.

    Is there a way I can select this using cursors or would I have to add counter variables to increment with each valid result? Basically what I want to do is select the jjobno's I need, use these results to select the appropriate records and then have a cumulative total for each 'Y' answer on the r.resp field for each question (r.serial) in the questionnaire.

  4. #4
    Join Date
    Jun 2003
    Posts
    20
    and cumulative totals on the 'N' and 'NA' answer too

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    sounds like you want analytics and not group by.

    your second query/cursor would look something like this:
    PHP Code:
    SELECT 
    (CASE WHEN r.resp 'Y' THEN COUNT(*) over (ORDER BY r.serialc.cb_textr.resp)ENDYES,
    (CASE 
    WHEN r.resp 'N' THEN COUNT(*) over (ORDER BY r.serialc.cb_textr.resp)ENDNO,
    (CASE 
    WHEN r.resp 'NA' THEN COUNT(*) over (ORDER BY r.serialc.cb_textr.resp)ENDNA,
    COUNT(r.respover (ORDER BY r.serialc.cb_text,r.respPHONED,
    r.serial,
    c.cb_text
    FROM cb_response r
    cb_jobdata jcb_items c
    WHERE r
    .serial c.serial 
    AND r.jjobno j.jjobno 
    AND r.jobseq j.jobseq 
    AND j.jclcode IN ('HR''HB''HS')
    AND 
    r.jjobno v_jjobno
    GROUP BY r
    .serialc.cb_text
    ORDER BY r
    .serial
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jun 2003
    Posts
    20
    Would that work ok with Oracle version 7.3? I have another query regarding your answer as I'm not too familiar with using CASE statements. Would this solve my problem of the results being broken down by jjobno? Or would this count each 'Y'. 'N' and 'NA' answer for each job number? What I'd like is:

    Serial Y N NA
    555 8 10 5
    345 8 10 8


    What I'm getting is:

    JJOBNO 5677

    Serial Y N NA
    555 4 5 2
    345 4 5 4

    JJOBNO 5678

    Serial Y N NA
    555 4 5 3
    345 4 5 4


    Thanks for your help again and I hope you can make some sense of this!

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Oracle 7?!?!?!?!??!?!?!?!??!?!?
    Get out of the stone age.


    Analytics are not supported on Oracle 7 so you are out of luck there.
    I have no idea what Oracle 7 is capable of.

    At this point, I would suggest running counters in your loops
    in your procedure you are writing.

    I don't think you can do it any other way with the version you are running.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jun 2003
    Posts
    20
    Yes I know maybe you could tell my employers that! Thanks for your help, I'll give it a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •