Hello JK, Using cursor you can do it.For the cursor sql. Use the 2 tables and have the join condition for them in place. You can then use the cursor loop,to check for any other validation and load data into the third table. Let me know in case,my response be detailed.
Thank you KS On Mon, Aug 1, 2011 at 11:46 AM, JK <[email protected]> wrote: > I have 3 tables as follows. I have to load the third table in a > specific way. > > > CREATE TABLE tmp_TABLE1 ( > patient_id NUMBER(1), > agency_number VARCHAR2(5), > case_number VARCHAR2(5) > ); > INSERT INTO table1 VALUES (1, '1111', '1212'); > INSERT INTO table1 VALUES (2, '2222', '2323'); > INSERT INTO table1 VALUES (3, '3333', '2424'); > INSERT INTO table1 VALUES (4, '4444', '3434'); > INSERT INTO table1 VALUES (5, '5555', '4545'); > INSERT INTO table1 VALUES (6, '6666', '5656'); > Create table tmp_table2( > agency_number VARCHAR2(5), > case_number VARCHAR2(5), > code1 number(2), > code2 number(2), > code3 number(2) > ); > INSERT INTO table2 VALUES ('1111', '1212',01,02,03); > Insert into table2 values ('2222', '2323',02,04,05); > Insert into table2 values ('3333', '2424',02,05,06); > > Create table table3( > patient_id NUMBER(1), > code number(2) > ); > > I have to load the table3 from tmp_table1 and tmp_table2: On querying > table3 it should look like the following. Is there as efficient way of > doing this in PL SQL, whether using a cursor or not. > select * from table3 > PATIENT_ID.................................. CODE > 1.....................................................1 > 1.....................................................2 > 1.....................................................3 > 2.....................................................2 > 2.....................................................4 > 2.....................................................5 > 3.....................................................2 > 3.....................................................5 > 3.....................................................6 > > I am using Oracle 10g. > > Your help will be appreciated. > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
