SQL & PL/SQL :: Using Correlated Values In Nested Subquery
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?
I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.
Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:
Left outer join. Works but its incredibly slow:
SELECT distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM", count(distinct ule.id) as "User Logins", count(distinct ule.user_id) as "Users logged on", count(distinct p2.id) as "Existing Users", count(distinct p1.id) as "New Users", count(distinct r1.id) as "Resources created"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select distinct ule.month as "Month", count(distinct p1.user_id) as "Users created", count (ule.id) as "Logins", count (distinct ule.user_id) as "Users logged in", count(rv.id) as "Resource Views", count(distinct rv.resource_id) as "Resources Viewed"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select distinct ule.month as "Month", count(distinct p1.user_id) as "Users created", count (ule.id) as "Logins", count (distinct ule.user_id) as "Users logged in", count(rv.id) as "Resource Views", count(distinct rv.resource_id) as "Resources Viewed"
[code]....
another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.
select distinct ule.loginday as "Month", count(distinct ule.id) as "Logins", count(distinct ule.user_id) as "Users logged in", count(distinct p1.user_id) as "Users created", count(distinct p2.user_id) as "Existing users1"
[code]....
performance is OK with 2 tables but the example above takes forever to execute.
Tried an approach with union but this creates new rows for each table
SELECT DISTINCT p1.MONTH AS "Month", COUNT(DISTINCT p1.user_id) AS "Users created", NULL AS "Logins", NULL AS "Users Logged in", NULL AS "Resource views", NULL AS "Resources viewed" FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64); CREATE TABLE department ( courses CourseList) NESTED TABLE courses STORE AS courses_tab; INSERT INTO department (courses)VALUES (CourseList('1','2','3'));
[code]....
The query returns the correct data, CourseList that are not subset of any other CourseList of the table.
I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.
I was making some variations of this code :
select d1.courses c_1, d2.courses c_2 from department d1,department d2 where d1.courses<>d2.courses(+);
Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT ( PONo NUMBER, CUST_ref REF Customer_objtyp, OrderDate DATE, ShipDate DATE,
[code]...
The LineItemList_ntab is a nested table.
Here is the create code for LineItemList_ntabtyp:
CREATE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER )
[code]...
In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.
Here is some of the code I have tried to insert values with:
INSERT INTO PurchaseOrder_objtab SELECT 1008, REF(C), SYSDATE, '12-MAY-1999', LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004, NULL
[code]...
The first insert statement above produces the following error:
SQL Error: ORA-00933: SQL command not properly ended
The second insert statement above produces the following error:
SQL Error: ORA-00936: missing expression
successfully insert values into the LineItemList_ntab nested table?
How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then variable := store_me_in_varray /* OR */ variable := array_type(sector.sector_name) i := i + 1; end if;
/* Now we output our varray or table */ start loop output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a, (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))
set b.answer = a.answer;
My second question is: The following query is giving error. What can I do?
update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55), b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;
Simplifying the data structure that my problem concern, let's say there are two materialized views between whose data there's a one-to-many relationship [the relationship can be logical without a need of creating any foreing keys].
The data should be as actual as possible respecting the content of the master tables, let's say it shoul be refreshed every 5 minutes.
As far as I know, the jobs related to each snapshot, even if they have a START WITH and NEXT parameter set to the same value, work independently...So: what would be the best manner to synchronize the jobs so as to make sure the data of both snapshots are coherent?
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.
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
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
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
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 )
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
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.
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 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?
/* (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.
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.
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?
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.
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
