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.
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.
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 Relatedbelow 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]....
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]...
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.
which one is better in performance point of view and why between inline view and correlated query.
View 5 Replies View RelatedCan 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":
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 RelatedIs 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;
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].....
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.
difference between view and materialized view?
View 1 Replies View Relatedwhat would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
View 7 Replies View RelatedI want basics of snapshot...creation,deletion,check...etc and what main difference in snapshot and view..?
View 2 Replies View RelatedWhat is the difference between Materialized view and dblink. When we use materialized view and when we use dblink. In my knowledge mviwes and dblinks are used to retrieve data from remote database.But I am unable to understand when should we materialized views and when should we use dblinks.
View 1 Replies View Relatedthe difference in detail with an example.
View 5 Replies View Relatedwhat is the difference between materialized view and snapshot ?
View 1 Replies View RelatedI have 3 sites which want to be updated as soon as the other one updates its data.(in 5 seconds). So I have chosen multi master solution,sync regardless of the resource consideration.
1-what is the difference between multimaster async and updatable materialized view regardless of resource consuming in multimaster?because in both of them each site can receive and also propagate the data...I just know that in materialized view we can work offline and then apply it manually is there any other thing?
2-I can not understand the difference between multimaster sync and async (considering the 5 seconds,because 5 second is a little time)I mean in this case are they different from each other or not.
[URL]...
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id
pt.party_id as id_klienta_mdm,
pt.master_reference_no as id_klienta_ref_mdm,
[code]...
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?
When to use inline query and to use join.
View 3 Replies View Relatedhow 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?
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]....
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 RelatedDetail table will look like below:
Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....
Need the data like
Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)
10/10/2012 3 4 1 4 2 1 0 0
How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?
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
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
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 )