How to do multiple operations on a single select in pl/sql

From: Jay <mighty_dragon_at_hotmail.com>
Date: Thu, 21 Oct 1999 12:49:02 -0500
Message-ID: <380F520E.10E2A9E1@hotmail.com>

Hello,

I have been trying to do this in dynamic sql but no success. Here is the problem: Suppose I have the ff. 3 tables and would like to do the operations described in SQL_statements, HOW WOULD I DO THIS IN DYNAMIC SQL SO AS TO REDUCE CODE REPETITION??? Basically, instead of having 4 large statements, we need a loop such that for each visit to the row matching (id,first,last), we need to do 4 sub-matching on the 'e' columns.

table_master_copy

    id first last e1 e2 e3 e4

---

    0    john    doe        80    90    79    50
    1    jane    doe        90    90    90    100
    2    foo     bar        80    80    80    80
    3    joe     beach      70    69    60    50

...

table_new_copy

    id first last e1 e2 e3 e4

---

    0    john    doe        80    90    79    50
    1    jane    doe        85    85    85    85
    2    foo     bar        100   100   100   100
    3    joe     beach      70    69    60    50

...

table_corrections

    id first last old_value new_value

---

<empty>

SQL_statements

---

INSERT INTO table_corrections (id, first, last, old_value, new_value)

    SELECT table_master_copy.id,
           table_master_copy.first,
           table_master_copy.last,
           table_master_copy.e1,
           table_new_copy.e1

    from table_master_copy, table_new_copy     where table_master_copy.id=table_new_copy

            and table_master_copy.first=table_new_copy.first
            and table_master_copy.last=table_new_copy.last
            and table_master_copy.e1 != table_new_copy.e1;

...<repeat the above statment for e2,e3,e4> Received on Thu Oct 21 1999 - 12:49:02 CDT