I'm getting back more than I want. I need to get the latest row in the PS_ACAD_STDNG_ACTN that has the academic standing code for students.
I thought if I max the effdt, strm, and effseq I would get back only one row. Especially effdt since a academic status is hardly ever updated on the same date.
PS_NTSR_GF_STUFILE will have multiple emplid's for students taking classes. PS_ACAD_STDNG_ACTN should have the last standing status for each student..(PRO = Probation, DIS = Dismissed).
UPDATE PS_NTSR_GF_STUFILE a
SET a.NTSR_GF_ENRL_STAT = nvl((
SELECT b.GBSA_SUB1
FROM PS_GBSA_DTL b, PS_ACAD_STDNG_ACTN c
WHERE c.ACAD_STNDNG_STAT = b.GBSA_VALUE
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...
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
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 )
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);
/* (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'.
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 :
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.
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
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;
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
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.
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
This is a surprisingly common one I've found on the web...even on devshed forum
I am updating one table from another (Updating Table A from Table B):
Table A ID, Value -- ----- 1 A 1 A 2 B
Table B ID, Value -- ----- 1 Animal 2 Box
Table A (modified) ID, Value, Name 1 A Animal 1 A Animal 2 B Box
No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...
Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?
This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.
I want to update with single space how can we that.
I am giving the data as follows.
CREATE TABLE student(sname VARCHAR2(30));
INSERT INTO student VALUES('PRAKASH BABU'); INSERT INTO student VALUES('RAJESH KUMAR'); INSERT INTO student VALUES('POORNA CHANDAR'); INSERT INTO student VALUES('ANIL RAJ'); INSERT INTO student VALUES('ABHI RAM KONA'); INSERT INTO student VALUES('SRI TEJA MEDI');
Like this the table contains millions of rows.
I want to update the names like this.
PRAKASH BABU RAJESH KUMAR POORNA CHANDAR ANIL RAJ ABHI RAM KONA SRI TEJA MEDI
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
Can I achieve the merge (update-only) and delete in a single SQL statement?
merge into table_4 using (select table_3.col1 col1,table_3.col2 col2, from table_1, table_2, table_3 where [code].....
delete from table_3 where <records identified in the 'using' clause of above merge>;
i.e.
delete from table_3 where (table_3.col1,table_3.col2) in ( select table_3.col1,table_3.col2, from table_1, table_2, table_3 where join conditions..... other conditions .... group by table_3.col1, col1,table_3.col2 having count(*) >1 );
What I want is roughly as following :
WITH mywith as ( select table_3.col1,table_3.col2, from table_1, table_2, table_3 where join conditions..... other conditions .... group by table_3.col1, col1,table_3.col2 having count(*) >1 ) update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2 delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
