Thank you for your response. If the count of the patient is limited to a small number, single query is an option. But the count can go beyond some millions. Even using a cursor might cause performance issues. I wonder if there are more efficient functions in Oracle to resolve this issue. If so, I would appreciate if you could provide some examples of using those functions.
On Mon, Aug 1, 2011 at 9:05 PM, Vinh Buu To <[email protected]> wrote: > Hi JK, > > you can use a single query: > Insert Into table3(patient_id, code) > (Select patient_id, code1 > From tmp_table1 natural join tmp_table2 > Union All > Select patient_id, code2 > From tmp_table1 natural join tmp_table2 > Union All > Select patient_id, code3 > From tmp_table1 natural join tmp_table2 ); > > or using a cursor in plsql as well > -- > VinhBuu To > > > > > On Tue, Aug 2, 2011 at 2: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 > -- 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
