ORA-01427: Single-row subquery returns more than one row
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);Should you want to know what rows are returned more than once, you can use the subquery and do a grouping of the selected columns:
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
SQL> select n, count(*) from srs group by n having count(*)>1;
N COUNT(*)
---------- ----------
1 2
Welcome to our forum for Oracle error: ORA-01427 Add your own message
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 ???
iwant to insert more then one row in single statment
sanjay
(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
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"
(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
i used something like this:
UPDATE tape
set duration=
(select ...
);
the select inside the () returns one values for each row in my table.
how can i update my column duration with a result calculated with a select from another two columns?
if i use the form from above, it gibes me the error "ORA-01427: Single-row subquery returns more than one row ".
(select othertable.value1 + othertable.value2 from othertable where othertable.fk=duration.id)
Enter your message Hi
I am entering the query in update statement which is generating error as single row query generating many rows in update statement in oracle 10g
Update aa s
SET emp_name = (select emp_name from aa1 d where d.emp_no = s.emp_no);
I tried even this which is giving me same error
Update aa s
SET emp_no = (select emp_no from aa1 d where d.emp_name = s.emp_name)
where exists (select emp_no from aa1 d where d.emp_name = s.emp_name);
I want to update the emp_no if the emp_name is same actrually my file has emp_no and emp_name but the emp_name has the same names which will bring my query like this.
Can you please give me a working solution? I will appreciate your help. Thanks in advance
Sweety
If you have no other way of determing what makes an employee unique, it is difficult to solve.
As you only have a name to lookup, it can return more than 1 record.
Don't you have an extra column that can be used in the join such as q unique email address or logon account?
select DISTINCT COMMONPACKAGE.GET_COMMUNITYNAME(L1.COMMUNITYCODE) Community,
(select count(*) from leads where COMMUNITYCODE IN (SELECT COMMUNITYCODE FROM COMMUNITIES) and
Leaddate between '01-Apr-2008' AND '30-Apr-2008' GROUP BY COMMUNITYCODE) Leads ,
(select count(*) from leads L1,leadactivities L2 where L2.activityname like '%Tour%' AND
L1.communitycode IN (SELECT COMMUNITYCODE FROM COMMUNITIES)
AND L1.LEADID = L2.LEADID(+) and L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008' GROUP BY L1.COMMUNITYCODE) LeadsToTours ,
(select count(*) from leadactivities where activityname like '%Tour%' AND
COMMUNITYCODE IN (SELECT COMMUNITYCODE FROM COMMUNITIES) GROUP BY COMMUNITYCODE)Tours ,
(select count(*) from leads L1,leadactivities L2 where L2.activityname like '%Tour%' AND
L1.Status='Moved-In' AND L1.LEADID = L2.LEADID(+) and L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008'
and L1.communitycode IN (SELECT COMMUNITYCODE FROM COMMUNITIES) GROUP BY L1.COMMUNITYCODE) ToursToMoveIns ,
(select count(*) from leads L1,leadactivities L2 where L1.Status='Moved-In' AND L1.LEADID = L2.LEADID(+) and
L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008' AND
L1.COMMUNITYCODE IN (SELECT COMMUNITYCODE FROM COMMUNITIES) GROUP BY L1.COMMUNITYCODE) LeadsToMoveIns ,
TRUNC((select count(*) from leads L1,leadactivities L2 where L2.activityname like '%Tour%' AND
L1.communitycode IN (SELECT COMMUNITYCODE FROM COMMUNITIES)
AND L1.LEADID = L2.LEADID(+) and L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008' GROUP BY L1.COMMUNITYCODE)/
DECODE((select count(*) from leads where COMMUNITYCODE IN (SELECT COMMUNITYCODE FROM COMMUNITIES)
and Leaddate between '01-Apr-2008' AND '30-Apr-2008' GROUP BY COMMUNITYCODE),0,NULL),6)*100 PerLeadsToTours ,
TRUNC((select count(*) from leads L1,leadactivities L2 where L2.activityname like '%Tour%' AND
L1.Status='Moved-In' AND L1.LEADID = L2.LEADID(+) and L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008'
and L1.communitycode IN (SELECT COMMUNITYCODE FROM COMMUNITIES) GROUP BY L1.COMMUNITYCODE)/
DECODE((select count(*) from leads L1,leadactivities L2 where L2.activityname like '%Tour%' AND
L1.communitycode IN (SELECT COMMUNITYCODE FROM COMMUNITIES)
AND L1.LEADID = L2.LEADID(+) and L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008' GROUP BY L1.COMMUNITYCODE),0,NULL),6)*100 PerToursToMoveIns ,
TRUNC((select count(*) from leads L1,leadactivities L2 where L2.activityname like '%Tour%' AND
L1.Status='Moved-In' AND L1.LEADID = L2.LEADID(+) and L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008'
and L1.communitycode IN (SELECT COMMUNITYCODE FROM COMMUNITIES) GROUP BY L1.COMMUNITYCODE)/
DECODE((select count(*) from leads where COMMUNITYCODE IN (SELECT COMMUNITYCODE FROM COMMUNITIES) and
Leaddate between '01-Apr-2008' AND '30-Apr-2008' GROUP BY COMMUNITYCODE),0,NULL),6)*100 PerLeadsToMoveIns
from leads L1, leadactivities L2 where L1.COMMUNITYCODE IN (SELECT COMMUNITYCODE FROM COMMUNITIES)
AND L1.Leaddate between '01-Apr-2008' AND '30-Apr-2008'
and L1.LEADID = L2.LEADID(+)
can single row subquery use 'in' operator?
when u type sga,its shows fixed and variable size.
can u plz tell me what is this fixed and variable size
Fixed size is the size needed by Oracle for some internal structures and the log_buffer size.
Variable size are the sizes for the db_cache_sizes and different pools (shared pool, large pool, large pool reserved, streams pool, java pool, olap pool, ...)
You can get more details in the v$sgastat data dictionary view
thank u so much for the ans,can u plz tell me how to know which parameter is static or dynamic?
Check out following columns of the (g)v$parameter view:
ISSES_MODIFIABLE
ISSYS_MODIFIABLE
ISINSTANCE_MODIFIABLE
thank u so much for the ans & i found it really helpfull.
can u plz explain about cursor & pga?
hello there,
plz can u explain about the parse calls n executions with examples
I want to replace column value in the table main from the table real where the main table is court and real table column is court where both the tables casecd,year and no are same means where these three fields are same then the main.court should be replaced by the real.court.
Please give me solution for this:-
update main m
set m.court=(select r.court from real r,main m where m.casecd=r.casecd and m.year=r.year and m.no=r.no)
where exists
(select r.court from real r,main m where m.casecd=r.casecd and m.year=r.year and m.no=r.no)
update main m
set m.court=(select max(r.court) from real r,main m where m.casecd=r.casecd and m.year=r.year and m.no=r.no)
where exists
(select r.court from real r,main m where m.casecd=r.casecd and m.year=r.year and m.no=r.no)
set m.court=(select r.court from real r,main m where m.casecd=r.casecd and m.year=r.year and m.no=r.no and rownum =1)
where exists
(select r.court from real r,main m where m.casecd=r.casecd and m.year=r.year and m.no=r.no)
I'm getting the same error message as ORA-01427 when i run the following query, but please tell me what to change in my query:
SELECT ), , 'nls_date_language=english' , (select number_of_days ) weekdays from ( select , , number_of_days from ( ITS_SENSOR_15MIN ) )), (CASE 45 0 0 'm' IN , , , ) 1 END , (CASE 45 0 0 'm' IN , , , ) 1 END ) ) 24 , ) its_sensor_15min ) GROUP )
TO_CHAR (TRUNC (date_read, 'mm'
'MON-YYYY'
) month_year
- (trunc((number_of_days - first_weekend) / 7) + 1
- (trunc((number_of_days - first_weekend - 1) / 7) + 1)
extract(day from next_day(trunc(input_date, 'MM'), 'SATURDAY')) first_weekend
last_day(input_date) month_end
extract(day from last_day(input_date))
select to_date (date_read, 'YYYY-MM-DD') input_date from
COUNT
WHEN speed_avg <=
AND speed_avg !=
AND vol_sum !=
AND lane_type =
AND (hour_read || qtrhr_read)
('61', '62', '63', '64', '71', '72', '73', '74'
'81', '82', '83', '84', '153', '154', '161', '162'
'163', '164', '171', '172', '173', '174', '181'
'182'
THEN
) count_rows_less_than_45
( COUNT
WHEN speed_avg <=
AND speed_avg !=
AND vol_sum !=
AND lane_type =
AND (hour_read || qtrhr_read)
('61', '62', '63', '64', '71', '72', '73', '74'
'81', '82', '83', '84', '153', '154', '161'
'162', '163', '164', '171', '172', '173', '174'
'181', '182'
THEN
/ COUNT (DISTINCT asset_id || direction
/
) count_ratio
COUNT (DISTINCT asset_id || direction
FROM
WHERE TO_CHAR (date_read, 'fmday') NOT IN ('saturday', 'sunday'
BY TRUNC (date_read, 'mm'
Thanks
IN my exception block, i am trapping multiple row exception.
BEGIN
/*Query that will return more than one row*/
EXCEPTION
WHEN TOO_MANY_ROWS THEN
v_param := 1
WHEN NO_DATA_FOUND THEN
v_param := 2
END;
I am not able to trap then exception. it directly goes to the WHEN OTHERS block of main procedure.
What is the mistake i am making.
What happens when you put a WHEN OTHERS in your sub block and display the sqlcode?
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.