Oracle analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.
Questions tagged [oracle-analytics]
42 questions
10
votes
3 answers
Conditional SUM on Oracle
I´m tring to make a query with a conditional SUM. The SUM needs to get more than 15, after that reset it. Like this:
A | 3 | 3
B | 7 | 10
C | 6 | 16 -- ====
D | 5 | 5
E | 9 | 14
F | 3 | 17 -- ====
G | 8 | 8
How can I make this?
Fábio Almeida
- 276
- 3
- 11
8
votes
4 answers
Duplicating records to fill gap between dates
I need to do something really weird, which is to create fake records in a view to fill the gap between posted dates of product prices.
Actually, my scenario is a little bit more complicated than that, but I've simplified to…
L. Holanda
- 4,432
- 1
- 36
- 44
6
votes
5 answers
Sql (on Oracle) aging report by days
I need help writing a aging report on oracle.
The report should be like:
aging file to submit total 17
aging file to submit 0-2 days 3
aging file to submit 2-4 days 4
aging file to submit 4-6 days 4
aging file to submit 6-8 days…
JavaSheriff
- 7,074
- 20
- 89
- 159
4
votes
3 answers
Oracle analytic function - using FIRST_VALUE to remove unwanted rows
I believe the Oracle function FIRST_VALUE is what I need to be using based on these two questions:
SQL - How to select a row having a column with max value
Oracle: Taking the record with the max date
I have 3 tables that represent people associated…
Paul
- 19,704
- 14
- 78
- 96
2
votes
2 answers
Oracle Analytics Window
Given the following table
PAYMENT_Date TRANSACTION_TYPE PAYMENT_AMT
1/1/2012 P 184366
1/1/2012 R -5841
1/2/2012 P 941
1/3/2012 P …
Sumit
- 1,661
- 1
- 13
- 18
2
votes
1 answer
Using Oracle analytics for average
How should I use the analytics to give me a rolling average where the sample size changes?
create table MyVals (Item_no char(10), epoch number, Yield number, Skip_Period char(1), Reset_Period char(1));
insert into MyVals values ('A00001',1705, 12,…
lidbanger
- 147
- 4
- 10
1
vote
1 answer
Merging workbooks in OAD (Oracle Analytics Desktop)
Is there any way we can merge multiple canvases from different workbooks into a single project/workbook?
If we import a .dva file into OAD, it functions as a new separate workbook.
I wanted to use individual canvases.
Abdullah Tariq
- 55
- 7
1
vote
0 answers
Oracle Analytical Desktop Variable Prompt issue
Before Saving
After Saving
In Oracle Analytical Desktop, I am using Variable Prompt (Custom Visualization), After saving my canvas the dropdown values of Variable Prompt Disappear .Only the selected value left in the dropdown.
OAD can't retain…
Wajahat kayani
- 93
- 8
1
vote
2 answers
SQL query : How to retrieve minimum value from top to that row in Oracle
I need help to write a query in Oracle to get minimum value for each row, comparing the current row amount with the previous minimum value.
In another word, calculate minimum value for each row from top to that row, dataset for minimum function is…
Ash020292
- 29
- 2
1
vote
1 answer
Duplicated rows numbering
I need to number the rows so that the row number with the same ID is the same. For example:
Oracle database. Any ideas?
Vilius Gaidelis
- 430
- 5
- 14
1
vote
0 answers
Unknown error while importing .dva file in Oracle Analytics Server 5.5.0
I have a problem while I'm trying to import a .dva file generated from Oracle Analytics Desktop into Oracle Analytics Server. It speems that it doesn't come from any specific dva file, it happens with all the files I try to import. For example, the…
Adrián Lamela
- 11
- 1
1
vote
1 answer
Oracle 11g Analytics Functions SUM
I'm using an analytic function to calculate a rolling 24 hour spend amount per customer in my transaction table. The function used to work, however the trx_datetime field was recently changed from date to timestamp(9).
select sum(th.amount)
…
Bill Brasky
- 13
- 3
1
vote
1 answer
Dense_Rank function not work
I have a below query :
select id, firstname,LastName,Company, dense_rank() over (partition by company order by id )
from UserAdditionalData
where Company is not null
Here is the result that I am getting: http://prntscr.com/a9d454.
Dense_Rank…
Ashwani Kumar
- 21
- 6
1
vote
4 answers
Difference between the values of multiple rows in SQL
My table in SQL is like:-
RN Name value1 value2 Timestamp
1 Mark 110 210 20160119
1 Mark 106 205 20160115
1 Mark 103 201 20160112
2 Steve 120 220 20151218
2 Steve 111 210 …
sumeet agrawal
- 57
- 1
- 12
1
vote
1 answer
Oracle SQL Query for distributing payments
I am trying to achieve distribution of payments over multiple invoice transactions as part of a batch process. The invoice and payment transactions are kept in the same table, that acts as a transactions register.
So for eg. we have in our…
Angad
- 3,389
- 3
- 30
- 40