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

Jay <mighty_dragon_at_hotmail.com> wrote in message news:380F520E.10E2A9E1_at_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>

I try to avoid dynamic SQL wherever possible because it is detrimental to performance, but I could tell you what you need to do.

You could build a loop from value 1 to 4, and in this loop you build up a string that you will then parse and execute each time...

i.e.

v_cursor_str := '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.e'||v_col_count||', table_new_copy.e'||v_col_count||' 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.e'||v_col_count||' != table_new_copy.e'||v_col_count;

If you need info on how to use DBMS_SQL for this then let me know.

HTH Received on Sun Oct 24 1999 - 01:54:31 CEST