Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to do multiple operations on a single select in pl/sql

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

From: Conan Farrell <conan_at_(remove_this)as-if.com>
Date: 22 Oct 1999 12:34:20 +0100
Message-ID: <01bf1c79$681fd380$b501ce0a@Cfarrell.snn.ie.sykes.com>


Jay,
 Im not sure that this is the type of solution you where looking for, but if you replace the column "e1" with say &col1 in the insert statement, save it to a file ,"insert.sql",
create a new file "run.sql" as follows
def col1=e1
@insert
def col1=e2
@insert
def col1=e3
@insert
def col1=e4
@insert

if you then start run.sql it will do the four inserts.

Hope that helps

--
Conan Farrell

Jay <mighty_dragon_at_hotmail.com> wrote in article <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>
>
>
>
>
Received on Fri Oct 22 1999 - 06:34:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US