PL/SQL error: ORA-01427: single-row subquery returns more than one row

Jack Seaman
Jack Seaman used Ask the Experts™
on
Here is the code:

UPDATE sfbetrm
SET sfbetrm_ar_ind =
(SELECT fa_info.ok
FROM fa_info
WHERE sfbetrm_ar_ind NOT IN ('C', 'Y')
AND fa_info.ok = 'Y'
AND sfbetrm_pidm = fa_info.pidm)
WHERE '&&update' = 'Y' AND sfbetrm_term_code = '201620'
AND EXISTS
(SELECT 1
FROM fa_info
WHERE sfbetrm_ar_ind NOT IN ('C', 'Y')
AND fa_info.ok = 'Y'
AND sfbetrm_pidm = fa_info.pidm);

How can this be fixed?  Oddly, it had been working in the past.  We did update the Oracle level since it was last run.
The error is PL/SQL error: ORA-01427: single-row subquery returns more than one row.
Jack
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I thinking this modification would work.

UPDATE sfbetrm
SET sfbetrm_ar_ind =
(SELECT fa_info.ok
FROM fa_info, sfbetrm
WHERE sfbetrm_ar_ind NOT IN ('C', 'Y')
AND fa_info.ok = 'Y'
AND sfbetrm_pidm = fa_info.pidm
AND sfbetrm_term_code = '201620')


Any thoughts?
Senior Oracle DBA
Commented:
This is a data issue and would have nothing to do with an Oracle upgrade.

Run the sub-query.  It will return more than one record.  Only you can determine which record is correct.  There is something missing from your where clause that is causing the sub-query to return multiple records.

Author

Commented:
I rewrote the query and posted it eariler.  I tested it out and it seems to work.  The orginal query seems overly complicated.  Any thoughts?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

Commented:
Rewritten query is not the same as the original query.  Rewritten query has no where clause and would therefore update every row in the table.  I don't think that is the intention.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Just for kicks and giggles, highlight the last subqueries and hit execute, then highlight from the third line on down and execute, see how many rows are returned.   If more than one row, there's the error, as you're setting the subquery return set to a single column value.

If this is a data issue it's going to limit our ability to help, as experts here cannot connect to your data source and run queries.

btw Missing a final ); probably just a copy-paste issue.
johnsoneSenior Oracle DBA

Commented:
I don't see an unmatched parenthesis.  Not sure where you see it.  Look at a better formatted version of the query:
UPDATE sfbetrm 
SET    sfbetrm_ar_ind = (SELECT fa_info.ok 
                         FROM   fa_info 
                         WHERE  sfbetrm_ar_ind NOT IN ( 'C', 'Y' ) 
                                AND fa_info.ok = 'Y' 
                                AND sfbetrm_pidm = fa_info.pidm) 
WHERE  '&&update' = 'Y' 
       AND sfbetrm_term_code = '201620' 
       AND EXISTS (SELECT 1 
                   FROM   fa_info 
                   WHERE  sfbetrm_ar_ind NOT IN ( 'C', 'Y' ) 
                          AND fa_info.ok = 'Y' 
                          AND sfbetrm_pidm = fa_info.pidm); 

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
You are correct, I didn't see the right paren in this line -->  AND sfbetrm_pidm = fa_info.pidm).

So my first comment should read 'highlight both subqueries sqparately and execute'.

Author

Commented:
The rewriten code does have a where clause.  I'm missing something?
johnsoneSenior Oracle DBA

Commented:
The rewritten query has a where clause in the sub-query.  The update doesn't have a where clause.  This would cause all rows in the table to be updated as there is no restriction on the update statement.

Author

Commented:
I've discovered what I think is the problem.  The script that creates the FA_info table inserts multiple records.  I looked as data from previous runs from a diferent sememter (term code) and there are no duplicates in the table.  There must be something wrong with the "unique index" too.
Here is the table creation and load script for the users.  Any thoughts?
DROP TABLE fa_info CASCADE CONSTRAINTS;
CREATE TABLE fa_info
(
pidm NUMBER (8),
ban_id VARCHAR2(36),
lname VARCHAR2(240),
fname VARCHAR2(240),
term VARCHAR2 (6),
disburse NUMBER (7, 2),
memo NUMBER (7, 2),
loan NUMBER (7, 2),
offer NUMBER (7, 2),
award NUMBER (7, 2),
ttuition NUMBER (7, 2),
dtuition NUMBER (7, 2),
scharge NUMBER (7, 2),
fees NUMBER (7, 2),
Housing NUMBER (7, 2),
Meals NUMBER (7, 2),
charge NUMBER (7, 2),
later NUMBER (7, 2),
bill NUMBER (7, 2),
cash NUMBER (7, 2),
diff NUMBER (7, 2),
track VARCHAR2 (6),
budget VARCHAR2 (6),
pack VARCHAR2 (6),
p_comp DATE,
preq_date DATE,
ver VARCHAR2 (1),
ver_comp VARCHAR2 (1),
progress VARCHAR2 (1),
isir VARCHAR2 (1),
rstate VARCHAR2 (1),
cert VARCHAR2 (1),
sap VARCHAR2 (6),
hold VARCHAR2 (1),
efc NUMBER (6),
major VARCHAR2 (6),
resident VARCHAR2 (1),
hours NUMBER (4, 2),
FA_HOURS NUMBER (4, 2),
attempt NUMBER (11, 3),
earn NUMBER (11, 3),
GPA NUMBER (4, 3),
ok VARCHAR2 (1),
part_of_term VARCHAR2 (1)
)
TABLESPACE users;
 
CREATE UNIQUE INDEX pk_fa_info
ON fa_info (pidm,term, part_of_term);
REM *************************************************** Student
INSERT INTO fa_info (pidm, ban_id, lname, fname, term, part_of_term)
SELECT DISTINCT
sfrstcr_pidm pidm,
spriden_id ban_id,
spriden_last_name lname,
spriden_first_name fname,
sfrstcr_term_code term,
CASE (ssbsect_ptrm_code) WHEN 'W' THEN ssbsect_ptrm_code ELSE NULL END
part_of_term
FROM sfrstcr, ssbsect,spriden
WHERE sfrstcr_term_code = '201620'
AND sfrstcr_rsts_code IS NOT NULL
AND sfrstcr_crn = ssbsect_crn
AND sfrstcr_pidm = spriden_pidm
AND spriden_change_ind IS NULL
AND sfrstcr_term_code = ssbsect_term_code;
COMMIT;

Author

Commented:
I've discovered why there are duplicates.  We have a 'winter' term that students pay for thru financial aid of the Spring.  So there is no need for seperate records for winter students.  When I pull out these records from the table, the other script should run fine.
johnsoneSenior Oracle DBA

Commented:
As a point of terminology...  There really isn't a unique index.  There is an index an a unique constraint.  You are creating the two at the same time with that statement you have.  Oracle has recommended (since somewhere around 9) that you separate the two like this:

create index pk_fa_info_idx
on fa_info (pidm,term, part_of_term);

alter table fa_info
add constraint pk_fa_info
unique (pidm,term, part_of_term);

This has the advantage of being able to disable and enable the constraint without having to rebuild the underlying index.

Also, if you had an index with more columns in it (for example pidm, term, part_of_term, ban_id), then this index could be used to enforce the unique constraint without having to duplicate the storage of an index.

Author

Commented:
Both experts gave me good insight to track down the cause of my error.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial