[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

PL/SQL cursor question

Posted on 2007-08-01
10
Medium Priority
?
1,101 Views
Last Modified: 2013-12-07
I am trying to correct this PL/SQL code (9i database) to update correctly.  I am getting the error ORA-01427: single-row subquery returns more than one row because there can be the same value for rcrapp3_yr_in_coll_2 for different rcrapp1_pidms/rcrapp3_pidms.

UPDATE rprlcrt
   SET rprlcrt_clas_code =
          (SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0')
 WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;
*********************************************************************************************************************************
I guess I need a cursor to cycle thru the table RPRLCRT for each ID and update with the nested subquery.  Here is my attempt to write a cursor.  Please advise

CREATE OR REPLACE PROCEDURE update_loans_1
IS
  v_rprlcrt_pidm  NUMBER;
  CURSOR c_select_pidm
  IS
     SELECT rprlcrt_pidm
      FROM RPRLCRT
     WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;

BEGIN
    OPEN c_select_pidm;

    LOOP
       FETCH c_select_pidm
          INTO v_rprlcrt_pidm;
            SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0'
                       AND rcrapp1_pidm = v_rprlcrt_pidm;
                       COMMIT;
            EXIT WHEN c_select_pidm%NOTFOUND;
     END LOOP;
END;




0
Comment
Question by:Jack Seaman
10 Comments
 
LVL 11

Assisted Solution

by:sujit_kumar
sujit_kumar earned 400 total points
ID: 19608866
you need to write a corelated sub-query (So that the join returns single row inside the sub-query.

Something like this,

UPDATE rprlcrt
   SET rprlcrt_clas_code =
          (SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0'
                       AND rcrapp1.<Foreign Key> = rprlcrt.<Primary Key> -- Change this as per your table structure.
  )
 WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19608896
change select to select distinct
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19609010
Well I would prefer the first approach, with the single update statement.

You get the ORA-01427 error because the subquery returns more than one rows.
sujit_kumar comment is basically correct: You don't seem to link the subquery with the external statement.
Weel I don't quite agree with the rcrapp1.<Foreign Key> = rprlcrt.<Primary Key> expression. It could be
 rcrapp1.<Primary Key> = rprlcrt.<Foreign Key> as well, but the main idea is that you need a link there.
 
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Jack Seaman
ID: 19609498
When I add "rcrapp3_pidm = rprlcrt_pidm", I get no records found.  I still think a cursor is necessary because each pidm can have rcrapp3_yr_in_coll_2 value.  For example, one pidm, can have a '2' and a different pidm can have a '2'.  They are both are correct.
0
 

Author Comment

by:Jack Seaman
ID: 19609577
I'm in error.  When I run it in my production database, (select only) I get records returned but the rcrapp3_yr_in_call_2 value can be say a '2' for different pidms.  Like my previous post.
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19609744
OK, lets start from the beginning. Try to give an example:

assume that you have two rows in rprlcrt, one has pidms=1 and the other pidms=2. The both have  rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL.

TO what value to you want to set rprlcrt_clas_code in each case?

If you can try the following in your production environment:

SELECT rprlcrt_clas_code,
          (SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0'
                       AND rcrapp3_pidm = rprlcrt_pidm) new_rprlcrt_clas_code
FROM rprlcrt
 WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;

Does it run or do you get an error? If not does it return any value or do you get null?

0
 

Author Comment

by:Jack Seaman
ID: 19609893
I want to set it to the matching value in the rcrapp3 table "rcrapp3_yr_in_coll_2".
When I run your code in production I get 214 records, some have blank new_rprlcrt_clas_codes.
When I run
SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0'
I get over 2400 records.
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19609987
The key is "matching value in the rcrapp3 table" How do you match it?
Is it with "rcrapp3_pidm = rprlcrt_pidm" ? If yes then is it possible that you don't have a matching row for some cases?

Since you get 214 records with my query, then those rows have
prlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL
Since some rows have null in new_rprlcrt_clas_codes, then for those rows there was no matching row  found in rcrapp3 table. If that is acceptable, then your update statement should be (I think you already tried it):

UPDATE rprlcrt
   SET rprlcrt_clas_code =
          (SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0'
                       AND rcrapp3_pidm = rprlcrt_pidm)
 WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;


0
 

Author Comment

by:Jack Seaman
ID: 19610031
I've updated my cursor:

/* Formatted on 2007/08/01 11:58 (Formatter Plus v4.8.6) ECSU-Admin Computing */
CREATE OR REPLACE PROCEDURE update_loans_1
IS
   v_rprlcrt_pidm   NUMBER;

   CURSOR c_select_pidm
   IS
      SELECT rprlcrt_pidm
        FROM rprlcrt
       WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;
BEGIN
   OPEN c_select_pidm;

   LOOP
      FETCH c_select_pidm
       INTO v_rprlcrt_pidm;

      UPDATE rprlcrt
         SET rprlcrt_clas_code =
                (SELECT rcrapp3_yr_in_coll_2
                   FROM rcrapp3, rcrapp1
                  WHERE rcrapp1_curr_rec_ind = 'Y'
                    AND rcrapp1_infc_code = 'EDE'
                    AND rcrapp1_infc_code = rcrapp3_infc_code
                    AND rcrapp1_seq_no = rcrapp3_seq_no
                    AND rcrapp1_aidy_code = '0708'
                    AND rcrapp1_pidm = rcrapp3_pidm
                    AND rcrapp3_aidy_code = '0708'
                    AND rcrapp3_yr_in_coll_2 <> '0'
                    AND rcrapp1_pidm = v_rprlcrt_pidm);

      EXIT WHEN c_select_pidm%NOTFOUND;
   END LOOP;

   CLOSE c_select_pidm;
END;
0
 
LVL 8

Accepted Solution

by:
YANN0S earned 1600 total points
ID: 19610164
Why do you insist on the cursor? The single update statement should be faster and the code is simpler.

Anyway, if you want to use the cursor, your code is NOT correct:
You loop within the cursor, but you update the entire table:

UPDATE rprlcrt
         SET rprlcrt_clas_code =
                (SELECT rcrapp3_yr_in_coll_2
                   FROM rcrapp3, rcrapp1
                  WHERE rcrapp1_curr_rec_ind = 'Y'
                    AND rcrapp1_infc_code = 'EDE'
                    AND rcrapp1_infc_code = rcrapp3_infc_code
                    AND rcrapp1_seq_no = rcrapp3_seq_no
                    AND rcrapp1_aidy_code = '0708'
                    AND rcrapp1_pidm = rcrapp3_pidm
                    AND rcrapp3_aidy_code = '0708'
                    AND rcrapp3_yr_in_coll_2 <> '0'
                    AND rcrapp1_pidm = v_rprlcrt_pidm);

If you want to use the cursor then if rprlcrt_pidm is unique in rprlcrt change the update statement to:
Why do you insist on the cursor? The single update statement should be faster and the code is simpler.

Anyway, if you want to use the cursor, your code is NOT correct:
You loop within the cursor, but you update the entire table:

UPDATE rprlcrt
         SET rprlcrt_clas_code =
                (SELECT rcrapp3_yr_in_coll_2
                   FROM rcrapp3, rcrapp1
                  WHERE rcrapp1_curr_rec_ind = 'Y'
                    AND rcrapp1_infc_code = 'EDE'
                    AND rcrapp1_infc_code = rcrapp3_infc_code
                    AND rcrapp1_seq_no = rcrapp3_seq_no
                    AND rcrapp1_aidy_code = '0708'
                    AND rcrapp1_pidm = rcrapp3_pidm
                    AND rcrapp3_aidy_code = '0708'
                    AND rcrapp3_yr_in_coll_2 <> '0'
                    AND rcrapp1_pidm = v_rprlcrt_pidm)
WHERE rprlcrt_pidm = v_rprlcrt_pidm;

Another approach is (If rprlcrt_pidm is not unique)
CREATE OR REPLACE PROCEDURE update_loans_1
IS
   v_rprlcrt_pidm   NUMBER;
   v_rowid ROWID;
   CURSOR c_select_pidm
   IS
      SELECT rprlcrt_pidm, rowid
        FROM rprlcrt
       WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;
BEGIN
   OPEN c_select_pidm;

   LOOP
      FETCH c_select_pidm
       INTO v_rprlcrt_pidm, v_rowid;

      UPDATE rprlcrt
         SET rprlcrt_clas_code =
                (SELECT rcrapp3_yr_in_coll_2
                   FROM rcrapp3, rcrapp1
                  WHERE rcrapp1_curr_rec_ind = 'Y'
                    AND rcrapp1_infc_code = 'EDE'
                    AND rcrapp1_infc_code = rcrapp3_infc_code
                    AND rcrapp1_seq_no = rcrapp3_seq_no
                    AND rcrapp1_aidy_code = '0708'
                    AND rcrapp1_pidm = rcrapp3_pidm
                    AND rcrapp3_aidy_code = '0708'
                    AND rcrapp3_yr_in_coll_2 <> '0'
                    AND rcrapp1_pidm = v_rprlcrt_pidm)
       WHERE rowid = v_rowid;

      EXIT WHEN c_select_pidm%NOTFOUND;
   END LOOP;

   CLOSE c_select_pidm;
END;

PS: And don't forget the commit at the end :)
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question