Thank you for your quick response.
If the count of the patient is more than a million, my concern is that the
cursor way might become inefficient. Is using MERGE a better way of
resolving this issue? If so, how is it used here? Is there any other more
efficient functions in Oracle?
Thanks,
JK
On Mon, Aug 1, 2011 at 8:48 PM, Kilaru S <kilaru.ora...@gmail.com> wrote:
> 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 <jyka...@gmail.com> 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 Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> 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 Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
