I'm going to learn more about analytical functions..

Thanks..

When I used the analytical function, I got this error:

ORA-00439: feature not enabled: OLAP Window Functions

How can I enable this feature?

Thanks in advance..

Tom,

Wouldn't this work, or is 'order by in an inline view' only available in Enterprise Edition?

SELECT q1.s,
q2.d-q1.d diff
FROM (SELECT s,
d,
rownum r
FROM t
ORDER BY d) q1,
(SELECT s,
d,
rownum r
FROM t
ORDER BY d) q2
WHERE q1.r = q2.r(+)-1
/

Tom,

I was trying to execute the analytical function lead within a PL/SQL block, but could not make it work the way it did as an Single SQL statement


SQL> create table emp(emp_no Number, Name VARCHAR2(20), hiredate date);

Table created.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_NO                                             NUMBER
 NAME                                               VARCHAR2(20)
 HIREDATE                                           DATE

SQL> insert into emp values(1000, 'Michael Owen', '01-MAR-1999');

1 row created.

SQL> insert into emp values(1001, 'David Beckham', '25-MAY-1999');

1 row created.

SQL> insert into emp values(1002, 'Ronaldinho', '03-MAR-2000');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

    EMP_NO NAME                                     HIREDATE
---------- ---------------------------------------- ---------
      1000 Michael Owen                             01-MAR-99
      1001 David Beckham                            25-MAY-99
      1002 Ronaldinho                               03-MAR-00

SQL> select emp_no, (lead(hiredate) over(order by emp_no) - hiredate) days from emp;

    EMP_NO       DAYS
---------- ----------
      1000         85
      1001        283
      1002

SQL> begin
  2  for x in (select emp_no, (lead(hiredate) over(order by emp_no) - hiredate) days from emp)
  3  loop
  4     dbms_output.put_line(x.emp_no||'  '||x.days);
  5  end loop;
  6  end;
  7  /
for x in (select emp_no, (lead(hiredate) over(order by emp_no) - hiredate) days from emp)
                                         *
ERROR at line 2:
ORA-06550: line 2, column 42:
PLS-00103: Encountered the symbol "OVER" when expecting one of the following:
. ( ) , * % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling

What could be the possible cause of this error... 

Tom,
I have a set of rows like this:

LOG_DATE LOG_LINE LOG_MODULE LOG_DESC
--------- -------- ---------- ----------------------------
01-OCT-03 1 CBP12600 Started 01-OCT-2003 09:50:34
01-OCT-03 2 CBP12600 Finished 01-OCT-2003 09:54:23
02-OCT-03 1 CBP12600 Started 02-OCT-2003 09:18:07
02-OCT-03 2 CBP12600 Finished 02-OCT-2003 09:20:08
06-OCT-03 1 CBP12600 Started 06-OCT-2003 14:47:55
06-OCT-03 2 CBP12600 Finished 06-OCT-2003 14:47:58

From which I wish to create a set of rows like this:

LOG_DATE LOG_MODULE DURATION
--------- ---------- --------
01-OCT-03 CBP12600 3:49 i.e. (09:54:23 - 09:50:34)
02-OCT-03 CBP12600 2:01 (09:20:08 - 09:18:07)
06-OCT-03 CBP12600 0:03 (14:47:58 - 14:47:55)

This would enable me to present the performance over time of any given batch
module as a graph using Discoverer. My current effort involves creating a view from a pl/sql function viz:
select * from TABLE(f_log_report()), and then querying the view for the
required module.
Could this reasonably be achieved using analytics? I haven't
had much luck! The log_date column contains the time portion, so it's not
necessary to go extracting it from log_desc. I'm using 9.2.0.3.0

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

ops$tkyte@ORA920LAP> select *
  2    from (
  3  select trunc(log_date) log_date,
  4         log_module,
  5             log_date start_log_date,
  6             lead(log_date) over (partition by log_module,trunc(log_date)
  7                                  order by log_line) end_log_date
  8    from t
  9         )
 10   where end_log_date is not null
 11  /

LOG_DATE           LOG_MODU START_LOG_DATE     END_LOG_DATE
------------------ -------- ------------------ ------------------
01-oct-03 00:00:00 CBP12600 01-oct-03 09:50:34 01-oct-03 09:54:23
02-oct-03 00:00:00 CBP12600 02-oct-03 09:18:07 02-oct-03 09:20:08
06-oct-03 00:00:00 CBP12600 06-oct-03 14:47:55 06-oct-03 14:47:58
 

After all the problems I had with this, the solution is surprisingly simple!

We wish to see the performance of an item after it has been brought on shelf .I am using rank to get all the sales ..

select item_id,sale_amount ,sale_dt,rank() over (partition by item_id order by start_dt ASC )rn from sales where item_id = kkkk

item_id sale_amount sale_dt
kkk 50 12/2/04
kkk 52.03 13/2/04
....


I now want to know only the sale in the first 30 days after the first_day ie after 12/2/04 + 30 days

Thanks