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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.
Qlemo"Batchelor", 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

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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.
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.

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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Can you explain why you selected that single comment only as answer?
Jack SeamanAuthor Commented:
I accepted 3 posts that helped with the answer.
Thanks,
Jack
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.