ORA-01427: Single-row subquery returns more than one row

    A subquery returns more than 1 row while only one row was expected.


    Either check that the data that is returned is correct, change the subquery so it returns maximum one row or allow more rows to be returned.


    In following example 2 rows matches the where condition. This will result in above error message.

    By changing the condition to use a IN relation operator, we prevent the error from being generated.


    SQL> create table srs(n number);

    Table created.

    SQL> insert into srs values(1);

    1 row created.

    SQL> /

    1 row created.

    SQL> select * from srs where n = (select n from srs where n=1);
    select * from srs where n = (select n from srs where n=1)
                                 *
    ERROR at line 1:
    ORA-01427: single-row subquery returns more than one row


    SQL> select * from srs where n in (select n from srs where n=1);

             N
    ----------
             1
             1

Adverteren bij Daisycon
Forum Messages
23-FEB-2008 01:55:21faceing problem of update multiple rowsPrateek Mathur Reply
query are :

update domst020 b set acct_int_paid_bal = (select sum(cert_int_paid_bal)
from domst040 d,domst020 c
where d.cert_subsys_cd=c.acct_subsys_cd and d.cert_ac_no=c.acct_ac_no
and d.cert_int_paid_bal >0 and d.CERT_SUBSYS_CD=b.acct_subsys_cd group by d.cert_ac_no ) where acct_subsys_cd='MIC';

error :
Single Row Subquery Returns More then one Row

tell me how to Update Multiple rows in Update Query in Oracle ???
: ->  24-MAR-2008 08:08:20insert querySanjay Pawar Reply
hi
iwant to insert more then one row in single statment

sanjay
02-MAY-2008 07:16:27nested queriesReshu_Ravi Reply
I have a table product. I want to narrow search based on modelname and productname. I am using my sql. Can anybody tell me how to do that. I think nested query will help me.
: ->  14-MAY-2008 08:44:21Please provide an exampleDbMotive Reply
Can you please provide an example of what you are trying to accomplish?
30-MAY-2008 08:16:36single-row subquery returns more than one rowJanaka Reply
select hp.party_name "Bill To",
(NVL2(LOC.ADDRESS1,REPLACE(LOC.ADDRESS1,',','')||','||CHR(10),'')
||NVL2(LOC.ADDRESS2,REPLACE(LOC.ADDRESS2,',',' ')||','||CHR(10),'')
||NVL2(LOC.ADDRESS3,REPLACE(LOC.ADDRESS3,',','')||','||CHR(10),'')
||NVL2(LOC.ADDRESS4,REPLACE(LOC.ADDRESS4,',','')||','||CHR(10),'')
||NVL2(LOC.CITY,LOC.CITY||',','')
||NVL2(LOC.STATE,LOC.STATE||',','')
||NVL2(LOC.PROVINCE,LOC.PROVINCE||',','')
||NVL2(LOC.POSTAL_CODE,LOC.POSTAL_CODE||','||CHR(10),'')
||NVL(TER.TERRITORY_SHORT_NAME,'')) "Address",
rct.trx_number "Invoice No",
rct.trx_date "Invoice Date",
rt.name "Paymnet Term",
rct.invoice_currency_code "Currency",
rctl.DESCRIPTION "Description",
rctl.QUANTITY_INVOICED "Qty",
rctl.UOM_CODE "UOM",
rctl.UNIT_SELLING_PRICE "Unit Prize",
( SELECT
SUM(TRXL.EXTENDED_AMOUNT)
FROM AR_VAT_TAX_ALL_B VAT,
RA_CUSTOMER_TRX_LINES_ALL TRXL
WHERE VAT.ORG_ID = TRXL.ORG_ID
AND VAT.VAT_TAX_ID = TRXL.VAT_TAX_ID
AND TRXL.LINE_TYPE LIKE 'TAX'
GROUP BY TRXL.CUSTOMER_TRX_ID,VAT.TAX_CODE) "Tax Amount" ,
rctl.EXTENDED_AMOUNT "Amount",
rct.COMMENTS "Comments" ,
rct.CUSTOMER_TRX_ID
from hz_parties hp,
ra_customer_trx rct,
ra_customer_trx_lines_all rctl,
RA_TERMS RT,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
HZ_CUST_SITE_USES HCSU,
HZ_CUST_ACCT_SITES HCAS,
RA_CUST_TRX_TYPES RCTT,
HZ_LOCATIONS LOC,
FND_TERRITORIES_VL TER
where hp.PARTY_ID = hps.PARTY_ID and
rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID and
rct.TERM_ID = rt.TERM_ID and
-- rct.trx_number = '3' and
rct.BILL_TO_CUSTOMER_ID = hca.CUST_ACCOUNT_ID and
hps.PARTY_SITE_ID = hcas.PARTY_SITE_ID and
rctt.CUST_TRX_TYPE_ID = rct.CUST_TRX_TYPE_ID and
rct.BILL_TO_SITE_USE_ID = hcsu.SITE_USE_ID and
hcsu.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID and
ter.TERRITORY_CODE = loc.COUNTRY and
loc.LOCATION_ID = hps.LOCATION_ID and
rctl.LINE_TYPE like 'LINE' and
rct.ORG_ID = :P_ORG_ID
order by hp.PARTY_NAME , rct.TRX_NUMBER

from the sub query out put has to be 100 and 150
for the tax amount



: ->  30-MAY-2008 09:13:47Not possibleDbMotive Reply
You cannot return 2 values in a column subquery.

Make sure this part only returns one row:

( SELECT
SUM(TRXL.EXTENDED_AMOUNT)
FROM AR_VAT_TAX_ALL_B VAT,
RA_CUSTOMER_TRX_LINES_ALL TRXL
WHERE VAT.ORG_ID = TRXL.ORG_ID
AND VAT.VAT_TAX_ID = TRXL.VAT_TAX_ID
AND TRXL.LINE_TYPE LIKE 'TAX'
GROUP BY TRXL.CUSTOMER_TRX_ID,VAT.TAX_CODE) "Tax Amount"

30-MAY-2008 09:31:03multiple row sub queryJanaka Reply
select hp.party_name "Bill To",
(NVL2(LOC.ADDRESS1,REPLACE(LOC.ADDRESS1,',','')||','||CHR(10),'')
||NVL2(LOC.ADDRESS2,REPLACE(LOC.ADDRESS2,',',' ')||','||CHR(10),'')
||NVL2(LOC.ADDRESS3,REPLACE(LOC.ADDRESS3,',','')||','||CHR(10),'')
||NVL2(LOC.ADDRESS4,REPLACE(LOC.ADDRESS4,',','')||','||CHR(10),'')
||NVL2(LOC.CITY,LOC.CITY||',','')
||NVL2(LOC.STATE,LOC.STATE||',','')
||NVL2(LOC.PROVINCE,LOC.PROVINCE||',','')
||NVL2(LOC.POSTAL_CODE,LOC.POSTAL_CODE||','||CHR(10),'')
||NVL(TER.TERRITORY_SHORT_NAME,'')) "Address",
rct.trx_number "Invoice No",
rct.trx_date "Invoice Date",
rt.name "Paymnet Term",
rct.invoice_currency_code "Currency",
rctl.DESCRIPTION "Description",
rctl.QUANTITY_INVOICED "Qty",
rctl.UOM_CODE "UOM",
rctl.UNIT_SELLING_PRICE "Unit Prize",
rctl.EXTENDED_AMOUNT "Amount",
rct.COMMENTS "Comments"
from hz_parties hp,
ra_customer_trx rct,
ra_customer_trx_lines_all rctl,
RA_TERMS RT,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
HZ_CUST_SITE_USES HCSU,
HZ_CUST_ACCT_SITES HCAS,
RA_CUST_TRX_TYPES RCTT,
HZ_LOCATIONS LOC,
FND_TERRITORIES_VL TER
where hp.PARTY_ID = hps.PARTY_ID and
rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID and
rct.TERM_ID = rt.TERM_ID and
rct.trx_number = '2' and
rct.BILL_TO_CUSTOMER_ID = hca.CUST_ACCOUNT_ID and
hps.PARTY_SITE_ID = hcas.PARTY_SITE_ID and
rctt.CUST_TRX_TYPE_ID = rct.CUST_TRX_TYPE_ID and
rct.BILL_TO_SITE_USE_ID = hcsu.SITE_USE_ID and
hcsu.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID and
ter.TERRITORY_CODE = loc.COUNTRY and
loc.LOCATION_ID = hps.LOCATION_ID and
rctl.LINE_TYPE like 'LINE'

above query generates two rows
how can i insert the subquery to this main query which is generates tax amount of 100 and 150

SELECT
SUM(TRXL.EXTENDED_AMOUNT) "Tax Amount"
FROM AR_VAT_TAX_ALL_B VAT,
RA_CUSTOMER_TRX_LINES_ALL TRXL,
ra_customer_trx rct
WHERE VAT.ORG_ID = TRXL.ORG_ID
AND VAT.VAT_TAX_ID = TRXL.VAT_TAX_ID
AND TRXL.LINE_TYPE LIKE 'TAX'
and rct.CUSTOMER_TRX_ID = trxl.CUSTOMER_TRX_ID
and rct.trx_number= '2'
GROUP BY TRXL.CUSTOMER_TRX_ID,VAT.TAX_CODE
Add your message for ORA-01427
Name:email:
Validation Code:6weg7muehnrb85305
Enter Code above:
Title:
State your problem: