PL/SQL :: Single Sub Row Query Returns More Than 1 Row?

Apr 19, 2013

I am trying to update values in a table from another table and getting the error: Single Sub Row Query Returns More Than 1 Row.

I want table B's PRV_NAME updated into table A's PRV_NAME where A.PRVID = B.PRVID where B.PRV_TYPE = M'

Both tables have all unique PRVID's, however, table B has PRVID's that have the same name. So table B data can look like this:

PRVID PRV_NAME
1234 PHOENIX MED
1235 SAC MED
1236 SAC MED
1237 OVERLAND
etc..

So, as you can see the PRVID's are unique, but not the PRV_NAME's. Is this the reason why I get this error?

I did not build the tables and have no control over what is put in them. If this is the reason for the error, is there any way to resolve this?

For reference, here is the query.

update msb_prv_source ps
set ps.prv_name =
(select prv00.prv00_prv_name
from prv00_prv prv00
join msb_prv_source ps
on prv00.prv00_prv_id = ps.prvid
where prv00.prv00_prv_type = 'M')

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Single Row Query Returns More Than One Row?

Oct 3, 2010

I am getting an error "Single row query returns more than one row" in an Exception block.

But in subqueries I am using IN operator not using =.

And I don't have duplicates rows in tables.

View 16 Replies View Related

PL/SQL :: Single Row Query Returns More Than One Row?

Mar 12, 2013

this is my sql=
" SELECT 

hpn.id AS id,
hpn.psn_id,
hpn.last_name,

[Code].....

"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,

but it returns error single row query returns more than one row, because one people can have more that one card_number.

View 9 Replies View Related

SQL & PL/SQL :: ORA 01427 - Single Row Sub Query Returns More Than One Record?

Oct 10, 2013

I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.

update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";

I understand that problem is there with inner select query.

View 3 Replies View Related

SQL & PL/SQL :: Write Single Row Sub-query Which Returns ID_2 Value Of Row With Del_flag As N

Feb 13, 2012

In my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.

Here is my Table:

ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888

So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.

select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?

Here, im passing ID_1 value from main query

View 8 Replies View Related

Single Row Subquery Returns More Than One Row

Dec 6, 2012

I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.

select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id

View 2 Replies View Related

Single-row Subquery Returns More Than One Row?

Dec 7, 2009

Just trying to update a table in which the sales amount is inserted when the sales amount is null.

I have

UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )

View 2 Replies View Related

Single-row Subquery Returns More Than One Row

Jan 10, 2012

I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.

 UPDATE ps_ntsr_gf_stufile a
    SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
    FROM PS_CLASS_TBL b
      WHERE a.CLASS_NBR = b.CLASS_NBR
      AND a.STRM = '1118'
      AND a.INSTITUTION = 'NT752')
      WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL); 

I'm still getting the ORA-01427 error.

View 1 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row

Mar 20, 2012

/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/

when i am trying to run this query i am getting an error'single row subquery returns more than one row'.

View 2 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row

Jul 30, 2010

My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :

SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....

SQL return : Ora-01427 Single-row subquery returns more than one row

Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,

View 13 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row?

Apr 28, 2012

I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept

It results in the below error.

[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row

I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)

I have written the below sql to fetch all loc for emp which got executed

SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP

But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.

View 3 Replies View Related

ERROR - Single-row Subquery Returns More Than One?

Apr 19, 2011

I am really new to oraclesql and I'm trying to run a simple update statement and I get the: ERROR single-row subquery returns more than one row:

UPDATE EMP_TYP_T A
SET A.ACTV_IND = (SELECT B.ISONBOARD FROM
TMPUSERS B
WHERE B.emp_id = A.emp_id);

View 4 Replies View Related

Error - Single Row Subquery Returns More Than One Row

Jul 28, 2003

What i'm trying to do is count the number of rows those sub-queries return.

As i understand it, that error happens when more than 1 row is returned.

Here is my query:

SQL> SELECT
2 a.ssg_nme,
3 b.sftw_prod_nme,
4 COUNT((SELECT db_srvr_id FROM inv.db_srvr_t WHERE node_id = (SELECT node_id FROM inv.node_t WH
ERE node_loc_cde IN (5, 7, 8)))),
5 COUNT((SELECT db_srvr_id from inv.db_srvr_t WHERE row_status_cde = 2)),
6 COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
7 FROM
8 inv.cde_ssg_t a, inv.sftw_prod_t b, inv.db_srvr_t c, inv.appl_t d, inv.db_t e
9 WHERE d.appl_id = e.appl_id
10 AND e.db_srvr_id = c.db_srvr_id
11 AND d.appl_ssg_cde IN (6, 9)
12 GROUP BY b.sftw_prod_nme, a.ssg_nme;
COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
*
ERROR at line 6: ORA-01427: single-row subquery returns more than one row

View 3 Replies View Related

SQL & PL/SQL :: ORA-01427 - Single-row Subquery Returns More Than One Row?

Jun 13, 2011

when i executr the query it gives a error message "ORA-01427: single-row subquery returns more than one row"

select distinct
a.CUSTOMER_NAME,
a.CUSTOMER_NUMBER,b.BILL_TO_CUSTOMER_ID,b.TRX_NUMBER,b.TRX_DATE,b.STATUS_TRX,c.ATTRIBUTE1 product,C.PAY_FROM_CUSTOMER,
(SELECT(SUM(E.AMOUNT))FROM RA_CUST_TRX_LINE_GL_DIST_ALL E WHERE 1=1 GROUP BY E.CUSTOMER_TRX_ID)
from ra_customers a,
ra_customer_trx_all b,
ar_cash_receipts_all c,
ar_payment_schedules_all d,
ra_cust_trx_line_gl_dist_all E
where a.CUSTOMER_ID=b.CUSTOMER_TRX_ID
and a.customer_id=c.PAY_FROM_CUSTOMER
and c.CASH_RECEIPT_ID=d.CASH_RECEIPT_ID
AND B.CUSTOMER_TRX_ID=E.CUSTOMER_TRX_ID;

View 2 Replies View Related

SQL & PL/SQL :: ORA-01427 - Single-row Subquery Returns More Than One Row

Dec 6, 2010

I recive next message: ORA-01427: single-row subquery returns more than one row

Here is my query: SELECT podaci FROM DET2 WHERE DET2.exportdate IN TO_DATE('12/03/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

Column PODACI is varchar2 type
Column EXPORTDATE is Date type

View 17 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row Error

Sep 16, 2011

I am relatively new to SQL and I am getting the below error, how to resolve this issue for my query?

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

nvl ( ( select add_qual_code from crm_add_qual WHERE tlc=q.tlc AND valid_since=q.valid_since AND ac_type_code=q.ac_type_code) , ' ') as add_qualification from qualification q

View 1 Replies View Related

SQL & PL/SQL :: 1427 / Single-row Subquery Returns More Than One Row

Feb 22, 2011

getting an error SQL error. Stmt #: 1604 Error Position: 786 Return: 1427 - ORA-01427: single-row subquery returns more than one row

modify the query

UPDATE ps_lm_ml_eli_d_tao
SET ( nid_special_char, last_name_srch, first_name_srch, dep_birthdate_c,
sex_flg )
=
(SELECT DISTINCT ps_dep_benef_nid.national_id,
Substr(ps_dependent_benef.last_name, 1, 20),

[code]....

View 15 Replies View Related

SQL & PL/SQL :: Select From - Single Row Subquery Returns More Than One Row

May 17, 2010

select * from course_section
where term_id = (select term_id from term where term_desc in ('Spring 2006','Spring 2007'));

and it is showing:

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

View 5 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row When Using NVL2

Jun 14, 2011

I have to fetch values from table conditionally.

create table tt(
userid varchar2(50),
aban8 varchar2(10)
)

insert into tt values ('1TESTX01', '111')
insert into tt values ('1TESTX02', '222')
insert into tt values ('1TESTX03', '333')

create table test1(
numval varchar2(50),
aban8 varchar2(10)
)

insert into test1 values ('12', '111')
insert into test1 values ('13', '222')
insert into test1 values ('14', '333')

The condition is to fetch userid. If a userid is provided and not null then it should return the userid provided. In this eg. '1TESTX01' If there is no userid provided then it should fetch userid from table tt with some condition...for simplicity I have not written condition but the inner query 'SELECT userid from tt' would return multiple records. And "userid IN" should be matched with the returning number of userids.

SELECT DISTINCT userid
FROM tt a, test1 b
WHERE a.aban8 = b.aban8
AND userid IN (
SELECT NVL2 ('1TESTX01', '1TESTX01',
(SELECT userid
FROM tt))
FROM DUAL);

This gives the error :-
ORA-01427: single-row subquery returns more than one row

Even running the query :-
select NVL2('1TESTX01', '1TESTX01',(select userid from tt )) from dual
gives the same error.

how to write the above query conditionally. I used case & decode too but got the same error.

View 9 Replies View Related

SQL & PL/SQL :: ORA-01427 - Single Row Subquery Returns More Than One Row Error

Apr 12, 2011

I have this sybase code below

UPDATE PS_LM_ML_ELI_D_TAO
SET NID_SPECIAL_CHAR =
H.NATIONAL_ID ,LAST_NAME_SRCH = SUBSTRING(F.LAST_NAME, 1 , 20),

[Code]....

i have changed the query as above.. but while running i see that i get subquery fetches more than on more. changing the query accordingly

View 1 Replies View Related

SQL & PL/SQL :: Update Queries / Single Row Subquery Returns More Than One Row

Feb 12, 2013

I have two update queries in the same Proc. One Seems to run just fine, the other I am getting this error:

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

The working Updates' structure is the same as the erroneous one. This works:

UPDATE P0525_STOREROOM_HOLDER H
SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID
FROM EMISTRATIFICATION_XS X
WHERE H.TOA = X.TOA
AND H.STOREROOM = X.STOREROOM
AND H.NSN = X.NSN
AND X.ASSEMBLY = 'NO REQUIREMENT' );

This one gives me a single-row error:

UPDATE P0525_STOREROOM_HOLDER H
SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID
FROM EMISTRATIFICATION_XS X
WHERE H.TOA = X.TOA
AND H.STOREROOM = X.STOREROOM
AND H.NSN = X.NSN
AND X.ASSEMBLY = 'ABOVE ALLOWANCE'
AND H.NSN_QUANTITY > 0);

I have run a check on the data and there doesn't appear to be any duplicate values in the second update... Both Updates are supposed to be updating record sets not a single row (i.e. the stratification_id where the criteria matches...

View 4 Replies View Related

PL/SQL :: Updating Project Details - Single Row Subquery Returns More Than 1 Row Error

Nov 13, 2013

I am trying to do this: 

UPDATE projectdetails SET projectdetails.projectname=(SELECT projectid FROM project WHERE projectdetails.projectname = project.projectname); 

but it returns with the error: single row subquery returns more than 1 row

I tried in the same PROJECTDETAILS table with another table A3, it works fine. but not for the PROJECT table. 

Below is the project table (i have 2055 rows) 

projectidprojectname1NEWEST2PARK WEST3THE VISION4PEOPLE'S PARK CENTRE5REFLECTIONS AT KEPPEL BAY6THE CLIFT7CLEMENTIWOODS CONDOMINIUM8HUNDRED TREES9PASIR VIEW PARK10CARABELLE11PARC IMPERIAL 

below is the projectdetails table: 

ProjectnameCORALS AT KEPPEL BAY20247502294883Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY278688018901475Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY15250502180700Strata101 to 05Jun-1399 yrs lease commencing from 20071ECHELON216298016471313Strata121 to 25Jun-1399 yrs lease commencing from 20121WHITEHAVEN135600012851055Strata101 to 05Jun-13Freehold1

View 14 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

SQL & PL/SQL :: Query Returns Multiple Rows / How To Get Distinct Row

Jul 18, 2010

in 10g report builder i have written this query

----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP

[code]....

this query returns multiple row. how to get distinct row.

View 5 Replies View Related

SQL & PL/SQL :: Select Statement - Inner Query Returns Data

Aug 23, 2012

I am new to oracle. I am creating a view, where in the inner query returns below kind data.

column1 column2
-------------------
a y
a y
b y
b n
c y
d n

I want to extract all column1 values which always has "y" in column2.

In this case, output has to be

output
------
a
c

View 10 Replies View Related

SQL & PL/SQL :: Function That Returns Source Tables From A Query

Oct 16, 2013

I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.

View 14 Replies View Related

SQL & PL/SQL :: Conditional Select - Query To Returns Results Based On Both City And Country Passed

Sep 17, 2010

Table A

Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo

Im looking for a query which returns results based on both city and country passed.

If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5

i.e. If country/city combination exists in DB return that row Else city row should be returned.

View 5 Replies View Related

SQL & PL/SQL :: Create A Query That Returns Record By Record A Field

Apr 21, 2010

I have the following case to solve:

Example Table:

Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00

I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:

Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00

View 8 Replies View Related

SQL & PL/SQL :: Getting Total And Single Name In One Query?

Oct 29, 2013

I want to get any employee name of deptno 10 but total count of number of employees under dept 10.

DECLARE
l_deptno NUMBER:=10;
l_count NUMBER;
l_ename varchar2(20);
BEGIN
SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno;
dbms_output.put_line(l_count||' '||l_ename);
end;

View 7 Replies View Related

PL/SQL :: Query To Find Single Row?

Oct 5, 2012

i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.

ID Course
103812     CFH     
102968     REP     
103812     DFH     
102968     DFH     
103071     DFH     
102968     CFH

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved