SQL & PL/SQL :: How To Use Window Functions In Where Clause Without Inline View
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
ADVERTISEMENT
Jan 5, 2012
I am trying to use RANK() clause with a window clause...is that possible to use both together?
like
select col1, col2, col3,
RANK() OVER (ORDER BY col3 desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RK
from table t
but getting error in SQL Developer
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 2 Column: 33
The reason why i need to rank in window clause because i have data like this
Name Marks Quiz
Ali 10 1
John 20 1
Sara 30 1
John 40 2
Sara 50 2
Ali 20 2
...
...
and so on
I want to rank them based on their cumulative sum marks after every test..ranking should be in such a way that it should look current row and preceding rows
like this
Name Marks Quiz cumulative_marks rk
Ali 10 1 10 4
John 20 1 20 3
Sara 30 1 30 2
Peter 100 1 100 1
John 40 2 60 3 ==> becuase John has now third most overall cumulative marks (60) after quiz 2.
Sara 50 2 90 2 ==> becuase Sara has now 2nd most overall cumulative marks (90) after quiz 2.
Ali 20 2 30 4 ==> becuase Ali has now fouth most overall cumulative marks (30) after quiz 2.
View 11 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
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
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
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
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
Jan 17, 2011
I have created a package with some functions in it.I want to create a view with from 2 tables but also i wanto to use the functions from some fields of the view. (e.g. if the wview has 10 fields the 5 of them will be from the 2 tables and the other from package functions).
View 2 Replies
View Related
Jun 13, 2010
currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :
Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.
The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.
but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.
View 5 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
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Feb 9, 2010
The following code works fine in the form 6i, but in the same form 10g I get map_handle='0' and win_handle='0' instead of certain value as in 6i.
map_handle:=get_item_property('MAP.MAP_ITEM',WINDOW_HANDLE);
win_handle:=get_window_property('CG$WINDOW_1',WINDOW_HANDLE);
MAP.MAP_ITEM has Chart Item type.
View 3 Replies
View Related
Sep 5, 2010
I have RMAN configured TO RECOVERY WINDOW OF 7 DAYS;I do a Level 0 backup on SAT, SUN-FRI I do a Level 1 (all to disk)
After I do a successful Level 0 backup, why would I need to keep Level 1 backups around on disk?I know the recovery window is 7 days but is there any way to remove the other 6 days of Level 1 backups after a successful Level 0?To me this seems like a waste of space since I have a succesful Level 0 or am I missing something?
View 6 Replies
View Related
Nov 27, 2004
code for "when button pressed" trigger that will close the current window and displays another window.
View 4 Replies
View Related
Feb 22, 2013
I want to call another canvas in same window.using this code /it not work show_view('canvasname');
View 1 Replies
View Related
Jan 15, 2011
i want to open a new window from my form. everything going well but when a new window opened. the control remains on previous form. and the new window is also inactive. i tried go_item with the item belongs to the new canvas but in vain.
View 14 Replies
View Related
Jul 23, 2010
I am using Developer 6i and i want to know how to replace the default icon in form window title bar with company's logo.
View 1 Replies
View Related
Apr 24, 2010
I have to create a new window in a newly developed forms 6i (in Oracle e Business Suite 11i).
This new window is to duplicate the Oracle standard alert systems. I have to create a new one to match the style classes of the other windows in the developed forms. Actually, I did not manage to change the oracle alert I used first, to match fonts, colour and sizes defined in the customised class. Thus, I decided to create a new window and canvas.
It has to display a confirmation message with 2 buttons: OK, Cancel. This window contains a canvas displaying a confirmation message, and 2 buttons : Ok, Cancel attached to my data-block. I manage to show this window, with a show_window command, called from a button on the previous window's canvas.
I do not manage to hide this new window, and to come back to the previous one, when I click on Cancel button. The window does not hide.
View 8 Replies
View Related
Mar 9, 2010
Some of the items are not getting displayed properly in my form when i run.I want all of them to appear on form. Can i maximize when i open.If so whats the command.
View 5 Replies
View Related
