Results 1 to 8 of 8

Thread: Cursor problem

  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Unanswered: Cursor problem

    Anyone any ideas how I can solve this problem? I have one query which selects stats I need and another which selects specific areas. I need to arrange the queries so that the stats are broken down by area. To get the areas I have to do more or less the same query as to get the stats as all the same filter criteria is used. It seems pointless to do the same query twice so is there away I can select the area in the same cursor as the stats and then arrange the output in for loops? For example here is what I have when using two cursors.

    for every_sc in selallscs loop --area
    v_jwccode := every_sc.jwccode;

    for every_rec in selallrecs loop --stats

    v_yes := every_rec.YES;

    What I'd like is:

    for every_sc in selallscs loop --area
    v_jwccode := every_sc.jwccode;

    for every_rec in selallscs loop --stats

    v_yes := every_rec.YES;

    Is this possible? It's really just to find away around querying for similar data twice as the tables are huge. Is there any way to do this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not like that no. Without knowing your full problem it is difficult to suggest a solution. Possibly bulk collecting all the data into arrays, if there isn't too much? But you say the tables are "huge", so maybe there will be too much?

  3. #3
    Join Date
    Jun 2003
    Posts
    20
    The problem is that I'm trying to speed up the report. I thought that if I could select all the information in one cursor rather than two it would speed things up a bit. The two cursors are identical apart from the fields being selected. As I mentioned earlier, the second cursor is only selecting the area field. So the report has to firstly scan through the whole table retrieve all the distinct area's and then the next cursor runs selecting all the records using the stats cursor. It just seems logical to try and tie in the two queries especially when the search criteria (everything in the where clause) is identical. I'm also using oracle version 7.3 so I'm not sure if I can use arrays? If you need me to be anymore specific please let me know and thanks for your help so far.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't really understand why you need to run the 2 copies of the query: one to get the areas, and one to get the data for each area. Why not just get all the data in the first query, sorted by area?

    If you could post your actual code that would help.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Generally, you'd like to have something like this:
    PHP Code:
    declare
      
    cursor c1 is 
        select empno
    enamejob
        from emp  
        order by job

      
    c1r c1%rowtype;
    begin
      open c1
    ;
      
    loop
        fetch c1 into c1r
    ;
        exit 
    when c1%notfound;
     
        
    dbms_output.put_line('Job : ' || c1r.job);
     
        
    /* Now you'd like to use the same c1 cusor and list all 
        employees under the same job. But, the cursor is already 
        open. You could close it and re-open, but you'd loose all
        the info you already collected.
        */
        
    ... 
    I guess you'll have to have 2 cursors. It seems (to me) to be the simpliest way to do it - nested cursor loops.
    Last edited by Littlefoot; 05-28-04 at 09:52.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, that seems to be what Starfield is trying to do. But surely the first query should be different to the second anyway, otherwise you would keep processing the same area (or job in this case) over and over again. I would have thought it would be more like:
    Code:
    declare
      cursor c1 is 
        select DISTINCT job
        from emp  
        order by job; 
      cursor c2 is 
        select empno, ename, job
        from emp  
        where job = p_job;
    begin
      for c1r in c1
      loop
    
        dbms_output.put_line('Job : ' || c1r.job);
    
        for c2r in c2 (c1r.job)
        loop
           ...
    Now rather than select the DISTINCT jobs from emp, it might be more efficient to do this:
    Code:
    declare
      cursor c1 is 
        select job
        from jobs
        where exists (select 1 from emp where emp.job = jobs.job)  
        order by job; 
      cursor c2 is 
        select empno, ename, job
        from emp  
        where job = p_job;
    begin
      for c1r in c1
      loop
    
        dbms_output.put_line('Job : ' || c1r.job);
    
        for c2r in c2 (c1r.job)
        loop
           ...
    But really, I don't see the need for 2 cursors anyway:
    Code:
    declare
      cursor c1 is 
        select empno, ename, job
        from emp  
        order by job; 
      l_prev_job emp.job%TYPE := '###';
    begin
      for c1r in c1
      loop
    
        if c1r.job != l_prev_job then
          dbms_output.put_line('Job : ' || c1r.job);
          l_prev_job := c1r.job;
        end if;
        ...

  7. #7
    Join Date
    Jun 2003
    Posts
    20
    Thanks for your help, I think the only way I can do it is using two cursors because of the formatting. It looks like Andrewst is on the right tracks with what I had in mind but I'm not sure if it will work that way. When I use the following code it gives me an error:

    for every_rec in selallrecs loop
    IF every_rec.jwccode != l_prev_jwccode THEN
    v_yes := every_rec.YES;
    v_no := every_rec.NO;
    v_na := every_rec.NA;
    v_cb_text := every_rec.cb_text;
    v_total := every_rec.total;


    IF v_total is not null then
    v_perc_y := ROUND((v_yes/v_total) * 100,0) ;
    v_perc_n := ROUND((v_no/v_total) * 100,0) ;
    end if;

    dbms_output.put_line('');

    print_results;
    print_summary;
    l_prev_jwccode := every_rec.jwccode;
    END IF;
    end loop;

    PLS-00306: wrong number or types of arguments in call to '!='

    for every jwccode (area) I want all the records to be printed out on a new page. I have procedures written for the formatting its just getting the query and loop arrangement to work together thats causing me the problem.

    The query I'm using is:

    CURSOR selallrecs IS
    SELECT SUM(decode(r.resp, 'Y', 1, 0)) YES,
    SUM(decode(r.resp, 'N', 1, 0)) NO,
    SUM(decode(r.resp, 'A', 1, 0)) NA,
    SUM(decode(r.resp,'N',1,'Y',1,0)) TOTAL,
    c.cb_text, j.jwccode
    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 c.q_number = v_q_number
    and (jclcode = v_jclcode or v_jclcode is null)
    and (jwccode = v_jwccode or v_jwccode is null)
    and (jcategory = v_jcategory or v_jcategory is null)
    and (jrepair_type = v_repair_type or v_repair_type is null)
    GROUP BY j.jwccode,c.cb_text
    ORDER BY j.jwccode;

  8. #8
    Join Date
    Jun 2003
    Posts
    20
    fixed the error that was my mistake, I'm still having problems with the formatting though

Posting Permissions

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