2 Replies Latest reply on May 17, 2010 6:13 AM by Saubhik

    PLSQL (FOR LOOP) with UNION

    734369
      I have a FOR Loop on a complex query with UNION ALL.

      When I run the query below, I get results in the DBMS_OUTPUT from the query in underline, but I get not results from the the other query formed with the UNION ALL.

      I cannot explain its behaviour. maybe I am doing something wrong. Is there a right way of doing this:


      FOR ROWDET IN (SELECT C1,C2, C3, C4 ,C5 from (
      SELECT C1, C2, C3,C4,C5 from TABLE A
      UNION ALL
      SELECT C1, C2, C3, C4, Test OVER (PARTITION BY TestCol order by Total desc) C5 from (
      SELECT C1 C2, C3, C4 ,Test, TestCol,Total from TABLE B
      )
      );
      LOOP
      v_print_row := '<tr>';
      v_print_row := v_print_row || '<td class=xl25>'|| ROWDET.C1||ROWDET.C2 || '</td>'||chr(10);
      v_print_row := v_print_row||'</tr>';
      DBMS_OUTPUT.PUT_LINE (v_print_row);
      END LOOP;
        • 1. Re: PLSQL (FOR LOOP) with UNION
          Tubby
          cimulation wrote:
          I have a FOR Loop on a complex query with UNION ALL.

          When I run the query below, I get results in the DBMS_OUTPUT from the query in underline, but I get not results from the the other query formed with the UNION ALL.

          I cannot explain its behaviour. maybe I am doing something wrong. Is there a right way of doing this:


          FOR ROWDET IN (SELECT C1,C2, C3, C4 ,C5 from (
          SELECT C1, C2, C3,C4,C5 from TABLE A
          UNION ALL
          SELECT C1, C2, C3, C4, Test OVER (PARTITION BY TestCol order by Total desc) C5 from (
          SELECT C1 C2, C3, C4 ,Test, TestCol,Total from TABLE B
          )
          );
          LOOP
          v_print_row := '<tr>';
          v_print_row := v_print_row || '<td class=xl25>'|| ROWDET.C1||ROWDET.C2 || '</td>'||chr(10);
          v_print_row := v_print_row||'</tr>';
          DBMS_OUTPUT.PUT_LINE (v_print_row);
          END LOOP;
          How about you prove this assertion with a small test case that we can run on our machines (CREATE TABLE statements and INSERT statements)?

          To that end, your Oracle version would be quite important to know
          select * from v$version;
          • 2. Re: PLSQL (FOR LOOP) with UNION
            Saubhik
            From this much of information, the only thing recommended is SET SERVEROUT ON !.