ORA-01427 single-row subquery returns more than one row

Question

I have query like this:

select bb10.ID_SEKTOR_10 as kode, 
bb10.NAME_SEKTOR_10 as sektor10,
(
    select count(*) as HUBBNI_SEKTOR_EKONOMI10 
    from bbcust_debitur_dev 
    where HUBBNI_SEKTOR_EKONOMI10 = bb10.ID_SEKTOR_10
    and STATUS = 2 
    group by HUBBNI_SEKTOR_EKONOMI10
) as total_sek_10,
(
    select  sum( distinct bbcd.baki_debet) as baki_debet
    from bbcust_cashloan_dev bbcd, bbcust_debitur_dev bdd
    where bbcd.row_id = bdd.row_id 
    and bdd.HUBBNI_SEKTOR_EKONOMI10 = bb10.ID_SEKTOR_10
    and bbcd.status =1 and bdd.status=2
    group by bdd.row_id
) as total_rek_10
from BBCUST_SEKTOR_10_DEV bb10;

I get an error

ORA-01427 single-row subquery returns more than one row

But before i just have data like this, i don't have any error. This query didn't have any errors.

select bb10.ID_SEKTOR_10 as kode, 
bb10.NAME_SEKTOR_10 as sektor10,
(
    select count(*) as HUBBNI_SEKTOR_EKONOMI10 
    from bbcust_debitur_dev 
    where HUBBNI_SEKTOR_EKONOMI10 = bb10.ID_SEKTOR_10
    and STATUS = 2 
    group by HUBBNI_SEKTOR_EKONOMI10
) as total_sek_10
from BBCUST_SEKTOR_10_DEV bb10;

And the result like this :

The stucture of bbcust_cash_loan like this:

For structure of database is : bbcust_debitur_dev have relation with BBCUST_SEKTOR_10_DEV and bbcust_cashloan_dev.

I want to calculate every value in bbcust_cashloan_dev, but I need to know all data in BBCUST_SEKTOR_10_DEV. That's why I join all data, so I get total value from bbcust_cashloan_dev where data in BBCUST_SEKTOR_10_DEV.

Can you tell me how to fix my query?

Just remove group by clause from your sub-query. Group by clause is not needed here. — Md. Shamim Al Mamun
@Md.ShamimAlMamun can you post on the answer question, i will vote for you, thanks. — user3505775

Md. Shamim Al Mamun Md. Shamim Al Mamun · Accepted Answer · 2016-01-20T09:12:42

Try this.......

select bb10.ID_SEKTOR_10 as kode, 
bb10.NAME_SEKTOR_10 as sektor10,
(
    select count(*) as HUBBNI_SEKTOR_EKONOMI10 
    from bbcust_debitur_dev 
    where HUBBNI_SEKTOR_EKONOMI10 = bb10.ID_SEKTOR_10
    and STATUS = 2 
    ) as total_sek_10,
(
    select  sum( distinct bbcd.baki_debet) as baki_debet
    from bbcust_cashloan_dev bbcd, bbcust_debitur_dev bdd
    where bbcd.row_id = bdd.row_id 
    and bdd.HUBBNI_SEKTOR_EKONOMI10 = bb10.ID_SEKTOR_10
    and bbcd.status =1 and bdd.status=2
    ) as total_rek_10
from BBCUST_SEKTOR_10_DEV bb10;

There is a possibility that group by bdd.row_id returns more than one row. Without knowing your business it is impossible to give suggestion.

ORA-01427 single-row subquery returns more than one row

1 Answers