Re: Running Insert Within Loop?

Thanks, You hit it on the head, I WAS planning on using temp tables. One question though then....excuse my ignorance...But then what is the easiest way to filter through results of a query?

I realized that the example I gave could have been done in single SELECT but what I am actually trying to accomplish is (as I am sure you understand) much more complex. I am mostly trying to get a handle on the PL/SQL syntax etc..

thanks again for your help...

billiauk_at_yahoo.co.uk (Adrian Billington) wrote in message news:<dee17a9f.0203270037.5618b342_at_posting.google.com>...
> I've supplied two solutions - PL/SQL and SQL. The PL/SQL solution
> "tidies" up your code and the SQL solution is what you should be
> doing. If you can do it in one SQL statement without resorting to
> procedural logic, then you should do so.
>
> I don't know much about SQLServer but I do know that SQLServer guys
> use "temporary tables" a lot as storage mechanisms for intermediate
> results. We don't have anything like this in Oracle - there are
> discussions about this on asktom.oracle.com. The reason I mention it
> is because the code you posted has what looks to be "working tables".
>
> Anyway, in the PL/SQL I've taken your explicit cursor and manual loop
> and turned it into an implicit "CURSOR FOR LOOP". We use these when we
> want to cycle through a cursor row by row. It handles all of the
> opening and closing of the cursor and will exit when there are no rows
> to fetch. It also provides you with a record based on the cursor - so
> this need not be declared either. You can also use these CURSOR FOR
> LOOPS with a cursor SQL declared in the DECLARE section. To reference
> this - FOR myrec IN mycursor LOOP (where mycursor is the SQL cursor
> you declared previously).
>
> Hope this helps. But you should really try to do this in SQL.
>
> Regards
>
> Adrian
>
> PL/SQL
> -------------------------------------------
> BEGIN
> FOR st_rec IN (SELECT state
> FROM master
> WHERE operation_type in (1,3)
> ORDER BY state) LOOP
>
> INSERT INTO tmptbl_table1a_totals
> SELECT state1
> , COUNT(state1)
> , SUM(prod1)
> , COUNT(state2)
> , SUM(prod2)
> FROM tmptbl_table1a_year1
> , tmptbl_table1a_year2
> WHERE state1 = state2
> AND state1 = st_rec.state
> GROUP BY state1;
>
> END LOOP;
> END;
> /
>
> SQL
> ---------------------------------
> INSERT INTO tmptbl_table1a_totals
> SELECT a.state1
> , COUNT(a.state1)
> , SUM(a.prod1)
> , COUNT(b.state2)
> , SUM(b.prod2)
> FROM tmptbl_table1a_year1 a
> , tmptbl_table1a_year2 b
> , master c
> WHERE a.state1 = b.state2
> AND b.state2 = c.state
> AND c.operation_type in (1,3)
> GROUP BY a.state1
> /
Received on Wed Mar 27 2002 - 16:49:12 CET