Limiting Subquery To First Record?
Oct 1, 2007
restricting a subquery's results to the first record. If I use the following:
SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE
I get
-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07
But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.
View 4 Replies
ADVERTISEMENT
Jun 10, 2010
I was reading a tutorial for analytical function and i found something like this
sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)
How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.
View 12 Replies
View Related
Nov 6, 2010
I have a database containing the following after entering the following sql command
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
TITLES.ROYALTY_RATE AS ROYALTLY_RATE,
AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE,
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T01 12446,34 ,05 1 Sarah Buchman
T02 190841,7 ,06 1 Sarah Buchman
T03 1025396,65 ,07 1 Christian Kells
T04 168882,99 ,08 ,6 Hallie Hull
T04 168882,99 ,08 ,4 Klee Hull
T05 1400008 ,09 1 Klee Hull
T06 225834 ,08 1 Wendy Heydemark
T07 35929790 ,11 ,5 Wendy Heydemark
T07 35929790 ,11 ,5 Klee Hull
T08 40950 ,04 1 Kellsey
T09 69750 ,05 1 Kellsey
TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T10 1 Wendy Heydemark
T11 752042,77 ,07 ,3 Hallie Hull
T11 752042,77 ,07 ,3 Klee Hull
T11 752042,77 ,07 ,4 Kellsey
T12 1299012,99 ,09 1 Wendy Heydemark
T13 313905,33 ,06 1 Sarah Buchman
17 rows selected.
What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME,
((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T01 12446,34 Sarah Buchman 622,317
T02 190841,7 Sarah Buchman 11450,502
T03 1025396,65 Christian Kells 71777,7655
T04 168882,99 Hallie Hull 8106,38352
T04 168882,99 Klee Hull 5404,25568
T05 1400008 Klee Hull 126000,72
T06 225834 Wendy Heydemark 18066,72
T07 35929790 Wendy Heydemark 1976138,45
T07 35929790 Klee Hull 1976138,45
T08 40950 Kellsey 1638
T09 69750 Kellsey 3487,5
TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T10 Wendy Heydemark
T11 752042,77 Hallie Hull 15792,8982
T11 752042,77 Klee Hull 15792,8982
T11 752042,77 Kellsey 21057,1976
T12 1299012,99 Wendy Heydemark 116911,169
T13 313905,33 Sarah Buchman 18834,3198
17 rows selected.
So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32
View 6 Replies
View Related
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
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
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
Jan 8, 2013
I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.
CREATE TABLE ACCT
(
ACCTNBR NUMBER(10)
) ;
[Code].....
In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?
View 8 Replies
View Related
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
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
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
Jun 5, 2010
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
View 5 Replies
View Related
Mar 31, 2010
The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:
SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid
But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.
SELECT DISTINCT tskid
FROM (SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid)
Is there any way to select the DISTINCT taskids ordered as per requirements?
View 13 Replies
View Related
Jun 5, 2011
A SINGLE SQL QUERY...
i have tried with no luck!!
consider this sample table:
NAME GENDER MARRIED
---------------------------
AAA M Y
BBB M N
CCC M Y
DDD F Y
EEE F Y
FFF M N
OUTPUT SHOULD BE:
GENDER TOTAL MARRIED
------------------------
MALE 4 2
FEMALE 2 2
TOTAL 6 4
View 24 Replies
View Related
Feb 10, 2013
Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
Eg:
R(x,y)
Select count(x)
from R
group by x;
Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
View 6 Replies
View Related
Oct 18, 2012
Can we use a subquery after the update keyword??
For example
UPDATE ( Select col1 from test)
set col1='x';
In short can we replace the table name after an update and use a select statement instead??
View 3 Replies
View Related
Feb 7, 2013
I want to display first joined and last joined employee name(first_name) in each department like this
department_name | first joined employee | last joined employee
I think we have to use joins and sub query.
employee table attributes -
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
[Code]....
View 1 Replies
View Related
Jul 26, 2012
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
View 3 Replies
View Related
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related
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
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
Mar 9, 2010
resolving this problem the query is
SELECT DAY_NBR ||' '||HOUR_NBR ||' '||MINUTE_NBR AS "@TIMEPOINT",
RTRIM ( xmlagg (xmlelement (c, procedure_nm || ', ')).extract ('//text()'), ', ' ) AS "@LABEL",
RTRIM ( xmlagg (xmlelement (c, procedure_id || ', ')).extract ('//text()'), ', ' ) AS "@PROCEDURE_ID",
cursor
(SELECT SUBJECT_NBR AS "@LABEL",TO_CHAR (PSUB.procedure_tm,'HH24:MI') AS "@TIME",
PROCEDURE_ID AS "@PROCEDURE_ID", PROCEDURE_SUB_ID AS "@PROCEDURE_SUB_ID"
FROM PROCEDURE_SUBJECT_TIME PSUB WHERE PSUB.ACTIVE_FLG = 1
AND PSUB.PROCEDURE_ID in pr.PROCEDURE_ID
ORDER BY PSUB.procedure_tm,PROCEDURE_ID
)AS VOLUNTEER
FROM SCHEDULE_PROCEDURE PR
WHERE active_flg = 1 and SCHEDULE_ID = 200900195269 group BY day_nbr,hour_nbr,minute_nbr,procedure_nm;
and the error getting is ORA-00979: not a GROUP BY expression
View 2 Replies
View Related
Sep 5, 2012
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
where max(b.effdt) between (SELECT term_begin_date AND term_end_date
from PS_TERM_TBL
[Code].....
View 2 Replies
View Related
May 22, 2012
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
[code]....
View 1 Replies
View Related
Sep 1, 2010
select rtrim(xmlagg(xmlelement(e, table_name||',')).extract('//text()'),',')
from (
select distinct table_name from tr_products tr join all_instruments v
on tr.PROD_COA_ID = v.PROD_COA_ID
where tr.LEVEL_06_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_05_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_04_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_03_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_02_VALUE like t1.COLUMN_VALUE||'%'
or tr.LEVEL_01_VALUE like t1.COLUMN_VALUE||'%')
This is an excerpt of a code of a huge query, I didn't want to write it all here because there is no point in doing that since this is the part of the query that I'm actually having problem with, and I think you will be able to understand my question just by looking at this piece of code.
Anyway, this here is actually a subquery in a select-clause of a main query, and apparently it has a (nested) subquery of its own (as seen in the code). I want to reach some values ("COLUMN_VALUE") that are correlated to the parent query (t1 is alias of a table in the from-clause of the main query), and as you can see, I'm trying to reach it from this level 2 subquery. As I have recently found out, Oracle does not allow this, it only tollerates correlation down to level 1.
Here is a scheme of my query:
select something1,
(select something2 from
(select something3 from some_table2 t2
where t1.value = t2.value))
from some_table t1
can this be overridden somehow, is there a workaround for this limitation?
View 3 Replies
View Related
Feb 26, 2010
I have a query that can return values or not:
If it returns a value, I want to print the value returned;
If it returns no value, I would like to return a string like 'NO_DATA'.
I have already tried several things, and the last one is:
==================================================================
Select NVL2(dnh.DiaNoHableData, dnh.DiaNoHableData, 'NO_DATA')
From (Select Dia_No_Habil || '#' ||
Trim(Descripcion) DiaNoHableData
From Dias_no_Habiles
Where Dia_No_Habil = To_Char(To_Date('20080704', 'YYYYMMDD'), 'YYYYMMDD')) dnh;
==================================================================
The subquery returns no data, so the first query should print 'NO_DATA', but it returns nothing.
View 3 Replies
View Related
Sep 4, 2011
I am working in EBS 11i and database 9i. I know that we can not write subquery in IF condition like below.
IF deptno IN (select deptno from dept)
--
END IF;
Is there any alternative to achieve above scenario.
View 1 Replies
View Related
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
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
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
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
