-
Help with setting a cursor to be used for a loop
Here is my problem. I need to insert a row into a table called form for each row in a table called patient. there are 86 thousand rows in the patient table. So I figured instead of 86 thousand insert statements, write 1 that does a loop. I figured I needed to set a cursor that holds the value from the patient table I need. here is what I have come up with so far:
create or replace procedure RESEARCH
is
begin
cursor p1 is
select "Patient" from PATIENT where "Patient" NOT IN ( select a."Patient" from PATIENT A, PFORM B where a."Patient"=b."Patient");
for PFORM in p1
loop
INSERT into PFORM values (p.p1, 'RESRCH', trunc(sysdate), '20240', PFORM_PFORM_SEQ.nextVal, 'Research Consent & Info', '0',p.p1, trunc(sysdate), '1', '20240', trunc(sysdate), '0','0','', ' ', ' ', '0', ' ');
end loop;
but when I go to create the package It fails because it doesn't like the p1 in the cursor line.
-
Originally Posted by jayjabour
Here is my problem. I need to insert a row into a table called form for each row in a table called patient. there are 86 thousand rows in the patient table. So I figured instead of 86 thousand insert statements, write 1 that does a loop. I figured I needed to set a cursor that holds the value from the patient table I need. here is what I have come up with so far:
create or replace procedure RESEARCH
is
begin
cursor p1 is
select "Patient" from PATIENT where "Patient" NOT IN ( select a."Patient" from PATIENT A, PFORM B where a."Patient"=b."Patient");
for PFORM in p1
loop
INSERT into PFORM values (p.p1, 'RESRCH', trunc(sysdate), '20240', PFORM_PFORM_SEQ.nextVal, 'Research Consent & Info', '0',p.p1, trunc(sysdate), '1', '20240', trunc(sysdate), '0','0','', ' ', ' ', '0', ' ');
end loop;
but when I go to create the package It fails because it doesn't like the p1 in the cursor line.
Not sure why a cursor is needed when a simple "insert into FORM from (select blah, blah, blah from PATIENT where wathever-condition-if-any) would solve it.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Sorry I am not explaining it well. I only need one fields value from the patient table. for example the form table has these fileds
form-
patient
form_code
date
pform
and the pateint table has these fields
patient
first
last
address
my insert for one patient is
insert into form values ('123', 'RESRCH', sysdate, '181281')
that would be for the patient who's number is 123 in the patient table. I have to insert a row in the form table for each patient in the patient table and the first value needs to be the patient number.
if I try :
insert into PFORM values ((select "Patient" p1 from PATIENT where "Patient" NOT IN ( select a."Patient" from PATIENT A, PFORM B where a."Patient"=b."Patient")), 'RESRCH', trunc(sysdate), '20240', PFORM_PFORM_SEQ.nextVal, 'Research Consent & Info', '0','783285', trunc(sysdate), '1', '20240', trunc(sysdate), '0','0','', ' ', ' ', '0', ' ')
which is what I think you sugested I get:
ORA-01427: single-row subquery returns more than one row
does this make more sense?
sorry for the confusion.
Last edited by jayjabour; 09-15-2010 at 04:13 PM.
-
Lets go to the basics, is this what you are looking for?
Code:
SQL>
SQL> create table PATIENT(
2 patient_nbr number(10));
Table created.
SQL> insert into PATIENT select 1 from dual;
1 row created.
SQL> insert into PATIENT select 2 from dual;
1 row created.
SQL> insert into PATIENT select 3 from dual;
1 row created.
SQL> insert into PATIENT select 4 from dual;
1 row created.
SQL> insert into PATIENT select 5 from dual;
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> create table FORM(
2 patient_nbr number(10),
3 some_date date);
Table created.
SQL> insert into FORM select 2,NULL from dual;
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from PATIENT;
PATIENT_NBR
-----------
1
2
3
4
5
SQL>
SQL> select * from FORM;
PATIENT_NBR SOME_DATE
----------- ---------
2
SQL>
SQL> insert
2 into FORM
3 select a.patient_nbr, sysdate
4 from PATIENT a
5 where (a.patient_nbr not in (select b.patient_nbr
6 from FORM b))
7 ;
4 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from FORM;
PATIENT_NBR SOME_DATE
----------- ---------
2
1 15-SEP-10
3 15-SEP-10
4 15-SEP-10
5 15-SEP-10
SQL>
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
haha, yup that works. However before I heard back from you I figured another way to do it. Much more involved. this is the script I used:
create table AXIUM.RESRCHTMP
(
"Patient" NUMBER(10)
);
insert into AXIUM.RESRCHTMP ("Patient") SELECT "Patient" FROM PATIENT WHERE "Patient" NOT IN ( SELECT a."Patient" FROM PATIENT A, PFORM B WHERE a."Patient"= b."Patient" and "FormCode"='RESRCH' );
ALTER TABLE AXIUM.RESRCHTMP
ADD (
"FormCode" CHAR(6 BYTE),
"Date" DATE,
"User" NUMBER(10),
"PForm" NUMBER(10),
"Description" VARCHAR2(30 BYTE),
"OwnerType" NUMBER(5),
"OwnerId" NUMBER(10),
"CreatedDate" DATE,
"Approved" NUMBER(5),
"LastAppUser" NUMBER(10),
"LastAppDate" DATE,
"Inactive" NUMBER(5),
"InactiveAppUser" NUMBER(10),
"InactiveAppDate" DATE,
"InactiveReason" VARCHAR2(50 BYTE),
"FStatus" CHAR(6 BYTE),
"AssignedUser" NUMBER(10),
"AssignedUGroup" CHAR(6 BYTE)
);
update RESRCHTMP set "FormCode"='RESRCH',
"Date"=trunc(sysdate),
"User"='20240',
"PForm"=PFORM_PFORM_SEQ.nextVal,
"Description"='Research Consent & Info',
"OwnerType"='0',
"OwnerId"="Patient",
"CreatedDate"=trunc(sysdate),
"Approved"='1',
"LastAppUser"='20240',
"LastAppDate"=trunc(sysdate),
"Inactive"= '0',
"InactiveAppUser"='0',
"AssignedUser"= '0';
INSERT INTO PFORM SELECT * FROM RESRCHTMP;
DROP TABLE RESRCHTMP;
It did what I needed, just more involved.
-
Originally Posted by jayjabour
haha, yup that works. However before I heard back from you...
Who's fault is that?
I solved within the hour, you let the ticket unattended for an entire week.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
it wasn't a dig on you. I came up with my method seeing as it worked (although not the best way) I didn't go back to the forum, for the solution. I got busy, today had some down time was looking around here saw the post and thought rather than not responding, maybe show another way. I guess next time I'll just say thanks and nothing more.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|