loop based on query result get error

Question

I have the result of a first query, and then based on that result I want to join with other tables so I can select the fields in the query result and other tables.

I construct the following

BEGIN
    FOR a_rec in (select col1, min(col2) as col2,col3 from a_tbl group by (col1, col3))
    LOOP
        select a_rec.col2, d_tbl.col4 
        from b_tbl b, c_tbl c, d_tbl d
        where b.col1 = a_rec.col1 and c.col5 = b.col5 and d.col6 = c.col6;
    END LOOP;
END;

then it reminds me that PLS-00428: an INTO clause is expected in this SELECT statement, so I create a temp table and the code becomes:

BEGIN
    FOR a_rec in (select col1, min(col2) as col2,col3 from a_tbl group by (col1, col3))
    LOOP
        select a_rec.col2, d_tbl.col4 into tmp_tbl
        from b_tbl b, c_tbl c, d_tbl d
        where b.col1 = a_rec.col1 and c.col5 = b.col5 and d.col6 = c.col6;
    END LOOP;
END;

then I get error like this PLS-00403: expression 'TMP_TBL' cannot be used as an INTO-target of a SELECT/FETCH statement

Maybe I should put the first query result into a temp table and then join it with other tables, although I wonder whether I can do it in such a procedure?

Answer 1

A SELECT INTO throws an error if the SELECT statement returns anything other than a single row. So that's clearly not what you want here. Since tmp_tbl is a global temporary table, you could do an INSERT SELECT in your loop

BEGIN
    FOR a_rec in (select col1, min(col2) as col2,col3 
                   from a_tbl 
                  group by (col1, col3))
    LOOP
      INSERT INTO tmp_tbl( col2, col4 )
        select a_rec.col2, d_tbl.col4 
          from b_tbl b, 
               c_tbl c, 
               d_tbl d
         where b.col1 = a_rec.col1 
           and c.col5 = b.col5 
           and d.col6 = c.col6;
    END LOOP;
END;

Since you say that you are just turning around and exporting the resulting data in SQL Developer, however, the temporary table doesn't seem particularly useful. You could remove all the PL/SQL and just write a single SELECT statement

    select a.col2, d.col4 
      from (select col1, min(col2) as col2, col3 
               from a_tbl 
              group by col1, col3) a,
           b_tbl b, 
           c_tbl c, 
           d_tbl d
     where b.col1 = a.col1 
       and c.col5 = b.col5 
       and d.col6 = c.col6;