PL/SQL single-row subquery returns more than one row

I have this code below but am getting this error: ORA-01427: single-row subquery returns more than one row
How can I fix?
SELECT spriden_id,
         tbraccd_pidm,
         tbraccd_term_code,
         spriden_last_name,
         spriden_first_name,
         spriden_mi,
         tbraccd_detail_code,
         tbraccd_amount,
         tbraccd_effective_date,
         tbraccd_desc
    FROM tbraccd, spriden
   WHERE tbraccd_pidm =
            (SELECT bannerid
               FROM wjseaman.tmp_bannerid)              
         AND tbraccd_pidm = spriden_pidm
         AND spriden_change_ind IS NULL
         AND (tbraccd_detail_code LIKE ('1%')
              OR tbraccd_detail_code LIKE ('2%'))
ORDER BY tbraccd_term_code, tbraccd_pidm;
Jack SeamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Your problem is that this query:

SELECT bannerid
               FROM wjseaman.tmp_bannerid)              
         AND tbraccd_pidm = spriden_pidm
         AND spriden_change_ind IS NULL
         AND (tbraccd_detail_code LIKE ('1%')
              OR tbraccd_detail_code LIKE ('2%')

returns more than 1 row.

I don't think we can tell you how to fix that.  Do If you want all records that match, then you could change the = to IN.  If you just care about one row, then possibly adding a ROWNUM=1 restriction.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
When using equal in WHERE, the subquery needs to return exactly one row, and your subselect certainly returns more than one (as it has no restriction at all). You probably want to use
WHERE tbraccd_pidm in (select bannerid from wjseaman.tmp_bannerid)
AND ...

Open in new window

0
johnsoneSenior Oracle DBACommented:
I cut and pasted too much of the query.  I missed a parenthesis.  It is that this returns more than 1 record:

SELECT bannerid
               FROM wjseaman.tmp_bannerid
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Others have pointed out the reason for the error.

If the query CAN return more than one row change it to:
...
 WHERE tbraccd_pidm in
            (SELECT bannerid
...

If you expect ONE row, then you have a query/data issue.  A DISTINCT might fix a query issue but when I see them as a solution, I think there is a larger problem somewhere.
0
Gerwin Jansen, EE MVETopic Advisor Commented:
>> SELECT bannerid FROM wjseaman.tmp_bannerid)
The tmp_bannerid columnname suggests that you have some other query or process inserting (temporary) bannerid's into that wjseaman table. Have a look there and see if you can explain why you get more than 1 record with the same bannerid. That's the issue you need to fix.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Can you explain why you selected that single comment only as answer?
0
Jack SeamanAuthor Commented:
I accepted 3 posts that helped with the answer.
Thanks,
Jack
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.