PL/SQL :: Correlated Update (Inline View)
Jan 21, 2013
Can I update inline view like below?
update
(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;
SQL Error: ORA-00904: "ANS"."ANSWER_ID":
View 8 Replies
ADVERTISEMENT
Apr 9, 2010
which one is better in performance point of view and why between inline view and correlated query.
View 5 Replies
View Related
Oct 6, 2012
I am working on this assignment question for class:
Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices for each vendor (just one for each vendor). Use an inline view that returns MAX(invoice_total) grouped by vendor_id, filtering for invoices with a balance due
What I have coded so far is below
SELECT i.vendor_id,
To_char(SUM(invoice_total), '$9,999,999.99') AS sum_invoice_total
FROM invoices i
join (SELECT vendor_id,
[code]...
I am getting some results which I have attached. I'm not sure if I'm capturing the "largest unpaid invoices for each vendor" part of the question. Yielding to the knowledge the group and trying to use the proper posting etiquette. I have attached my output screen as well.
View 1 Replies
View Related
Aug 19, 2013
the execution steps of inline view?importance&advantage of inline view?.Also reference link where i can get more information about inline view(i.e. basics to advanced),exercises and interview questions.
View 3 Replies
View Related
Sep 23, 2011
below query, first query is giving proper output i.e. 0 while second is giving wrong output i.e. 1.
Why this happen , what's wrong in using count in inline view ?
selecT count(*)
from (select *
from dual
where 1 = 2
union
select * from dual where 1 = 2);
[Code]....
View 6 Replies
View Related
Oct 1, 2010
there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.
following is a query
create table sam as
select * from
((
select distinct stck.item_code
from (
select item_code,bal
[code]...
View 4 Replies
View Related
Apr 8, 2010
I am working on Oracle 10g and Below is my query which is taking 30min. to execute. I am using two inline view and then make joins on these inline view because of i think it Degrade Performance.
Query :-
SELECT LEVEL_USER,
TRIAL_NO,
UNIT_NO,
COUNTRY_CODE,
PERSONNEL_NO,
[code].......
Note :- i have Attached the explain Plan for the same .Please let me know how can i improve performance of this Query.
View 3 Replies
View Related
Sep 6, 2012
Attached query is running fine if inline view B (Marked in Comments) returning value. If inline view B returns NULL then it fails to return result. I want if Inline view B is returning NULL then it should pass ZERO to main query.
View 2 Replies
View Related
May 10, 2013
Is there a function that allows the following?
select SOME_FUNCTION('N','E','S','W') from dual;
That returns
N
E
S
W
Currently I'm just doing the following
WITH direction AS
(SELECT 'N' dir FROM DUAL
UNION
SELECT 'E' FROM DUAL
UNION
SELECT 'S' FROM DUAL
UNION
SELECT 'W' FROM DUAL)
SELECT *
FROM direction;
View 4 Replies
View Related
May 16, 2013
Below is the query where I need to filter the data using window function.
I don't want to use inline view for mt query because i need to use the same query in OWB which it wont support.
Select a.Physical_Id,
a.Booking_Begin_Date,
a.Booking_End_Date,
[Code]....
I Cannot use below query in OWB Which is Inline View
----------------------------------------------------------------------------
Select b.* From
(Select a.Physical_Id,
a.Booking_Begin_Date,
[Code].....
View 2 Replies
View Related
Jul 7, 2012
After searching a lot on net the differences between inline view and subquery that i came across are
1) you can use order by in inline view but not in subquery.
2) you can use alias for inline view but not subquery.
i have to fetch the same outcome using both the functionality respectively, then what is the technical impact/better.
View 4 Replies
View Related
Apr 25, 2012
Just want to confirm, that where Oracle stores data fetched from inline view.
I am asking, as I am making an inline view on a very large table and its getting failed with TEMP space error.
View 3 Replies
View Related
May 20, 2010
9i worked fine 11g release2 giving ora-01779
alternative sql for the following :
UPDATE /*+ BYPASS_UJVC */
(
SELECT
c.c1,
c.c2,
c.c3,
[code].....
View 5 Replies
View Related
Sep 13, 2010
I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
View 15 Replies
View Related
Jul 18, 2012
I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:
CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)
i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.
View 26 Replies
View Related
Dec 7, 2012
I have big trouble with some Update query on Oracle 11G.I have a set of tables (5) of identical structures and a view that consists in an UNION ALL of the 5 tables.None of this table contains more than 20 000 rows.Let's call the view V_INTE_NE. Each of the basic table has a PRIMARY KEY defined on 3 NUMBERS(10,0) -> INTE_REF / NE_REF / INSTANCE.
Now, I get 6 rows in another table and I want to update my view from the data of this small table (let's call it SMALL). This table has the 3 columns INTE_REF / NE_REF / INSTANCE.
When I try to join the two tables :
SELECT * FROM T_INTE_NE T2
WHERE EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
I get the 6 lines in 0.037 seconds
When I try to update the view (I have an INSTEAD OF trigger that does nothing (just return for testing even without modifying anything), I execute the following query :
UPDATE T_INTE_NE T2
SET INTE_STATE = -11 WHERE
EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
The 6 rows are updated (at least TRIGGER is called) in 20 seconds.
However, in the execution plan, I can't see where Oracle takes time to achieve the query :
Plan hash value: 907176690
[code]....
Predicate Information (identified by operation id):
2 - access("T2"."INTE_REF"="T1"."INTEREF" AND "T2"."NE_REF"="T1"."NEREF" AND
"T2"."INTE_INST"="T1"."INSTANCE")
Note- dynamic sampling used for this statement (level=2)
Statistics
-----------------------------------------------------------
3 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin
[code]....
I get exactly the same execution plan (when autotrace is ON).Furthermore, if I try to do the same update on each of the basic tables, I get the rows updated instantaneously.
View 10 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
May 30, 2008
i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:
CREATE OR REPLACE TRIGGER a
after update on A@dirfep.us.oracle.com
FOR EACH ROW
BEGIN
[code]...
View 1 Replies
View Related
Nov 29, 2010
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?
View 3 Replies
View Related
Jul 18, 2013
concept of Exists, With and Correlated Subquery in Sql plus 10g from begining, what are they how do they work with step by step example from basic.
View -1 Replies
View Related
Aug 28, 2013
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
View 1 Replies
View Related
Sep 12, 2013
I am using sqlplus. example to update a base table using a updatable materialized view.
View 17 Replies
View Related
Aug 24, 2012
i have a little doubt in Materialized view. i created a materialized view and log with the following query,
create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);
[Code]...
Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with
*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*
But it throwing error,
Error starting at line 5 in command:
alter MATERIALIZED VIEW product_sales_mv AS SELECT b from test_mv
Error report:
SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+
*Cause:+
*Action:+
i guess i am doing wrong. i want to update it without drop and recreate
View 2 Replies
View Related
Jul 6, 2012
Is the identifier functions UID always correlated with the column dba_users.user_id ?
View 4 Replies
View Related
Jun 11, 2010
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,
[code]....
View 4 Replies
View Related
Jun 14, 2007
we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.
Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?
View 1 Replies
View Related
Jul 31, 2012
When to use inline query and to use join.
View 3 Replies
View Related
Dec 3, 2012
how joins work with in-line views.I have a query and its explain plan as below:
SELECT e.ename,e.deptno,d.dname FROM
dept d,
emp e
WHERE e.deptno=d.deptno
AND e.deptno=20
[code]....
I do not find any difference in both the explain plans. Both are same. In my second query, the filtered rows will be joined to dept table. And hence the baggage will reduce.But how can I verify that in-line view has worked better?
View 8 Replies
View Related
Jan 15, 2013
I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
[Code]....
View 7 Replies
View Related
Aug 20, 2013
I have created a few flash charts using APEX4.2. Is there a way for the user to view the chart and then email it as an inline image to someone? I know that the user can save the chart as PDF file, then send it as an attachment via Outlook ( or similar system), but can I integrate these manual steps to one ( template), similar to the IR report's download feature ?
View 0 Replies
View Related