Home » SQL & PL/SQL » SQL & PL/SQL » 'ORA-30487: ORDER BY not allowed here'
'ORA-30487: ORDER BY not allowed here' [message #291025] Wed, 02 January 2008 15:30 Go to next message
tekdoubts
Messages: 4
Registered: January 2008
Junior Member
I have a transaction table with
1. transaction id
2. customer id
3. merchant id
4. transaction date

Test table is as follows

CREATE TABLE sampletable
(txnid NUMBER(10,0) NOT NULL,
custid NUMBER(10,0) NOT NULL,
merchid NUMBER(10,0) NOT NULL,
txn_date DATE NOT NULL
);

ALTER TABLE sampletable
ADD CONSTRAINT sampletable_pk PRIMARY KEY (txnid);

INSERT INTO sampletable
VALUES
(1, 1, 1, to_date('01-01-2008', 'DD-MM-YYYY'));

INSERT INTO sampletable
VALUES
(2, 1, 1, to_date('31-12-2007', 'DD-MM-YYYY'));

INSERT INTO sampletable
VALUES
(3, 2, 1, to_date('31-12-2007', 'DD-MM-YYYY'));

INSERT INTO sampletable
VALUES
(4, 3, 1, to_date('30-12-2007', 'DD-MM-YYYY'));

INSERT INTO sampletable
VALUES
(5, 4, 1, to_date('29-12-2007', 'DD-MM-YYYY'));

INSERT INTO sampletable
VALUES
(6, 2, 1, to_date('28-12-2007', 'DD-MM-YYYY'));

INSERT INTO sampletable
VALUES
(7, 2, 1, to_date('28-12-2006', 'DD-MM-YYYY'));


For each transaction I want to find:

1. Number of transactions a particular merchant received in the previous 7 days (including the current transaction)
2. Number of unique customers who purchased goods from the merchant of the current transaction.

I am using Oracle 8.1.7.4.0

For the first question I used
select a.*, count(*)
over (partition by merchid
order by txn_date
range interval '7' day preceding) numrecv7days
from sampletable a;

and it gives me what I needed.

But for the second question when I try
select a.*, count(distinct custid)
over (partition by merchid
order by txn_date
range interval '7' day preceding) numcustomers7days
from sampletable a;

I get an error 'ORA-30487: ORDER BY not allowed here'

I went through the following exchange - http://www.orafaq.com/forum/t/85802/0/ - but am unable to adapt it to my situation.

Any help would be appreciated

Thanks,
Swami
Re: 'ORA-30487: ORDER BY not allowed here' [message #291027 is a reply to message #291025] Wed, 02 January 2008 15:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

ORA-30487: ORDER BY not allowed here
Cause: DISTINCT functions and RATIO_TO_REPORT cannot have an ORDER BY
Action: none

But your second question does not mention any date range, why there is one in your query?

Your first query does not answer your first question. If it gives the result you expect, then the question is wrong.

Regards
Michel

[Updated on: Wed, 02 January 2008 15:42]

Report message to a moderator

Re: 'ORA-30487: ORDER BY not allowed here' [message #291030 is a reply to message #291025] Wed, 02 January 2008 15:50 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps I didn't understand it correctly, but - wouldn't those simple queries do the job?
SELECT merchid, COUNT(*)
FROM SAMPLETABLE
WHERE txn_date >= TRUNC(SYSDATE) - 7
GROUP BY merchid;

SELECT merchid, COUNT(DISTINCT custid)
FROM SAMPLETABLE
GROUP BY merchid;
Re: 'ORA-30487: ORDER BY not allowed here' [message #291032 is a reply to message #291027] Wed, 02 January 2008 16:01 Go to previous messageGo to next message
tekdoubts
Messages: 4
Registered: January 2008
Junior Member
Sorry I think I should amend the questions:

1. Number of transactions received by the merchant of the current transaction in the past 7 days.

for the sample table I'd used the result is (sorrty:

TxnId Custid Merchid TxnDate numrecv7days
7 2 1 28-DEC-06 1
6 2 1 28-DEC-07 1
5 4 1 29-DEC-07 2
4 3 1 30-DEC-07 3
2 1 1 31-DEC-07 5
3 2 1 31-DEC-07 5
1 1 1 01-JAN-08 6

which is what I need. (is the format okay?)

2. Number of distinct customer who sent money to the merchant of the current transaction in the past 7 days


Re: 'ORA-30487: ORDER BY not allowed here' [message #291034 is a reply to message #291030] Wed, 02 January 2008 16:09 Go to previous messageGo to next message
tekdoubts
Messages: 4
Registered: January 2008
Junior Member
I have to do this at a transaction level, not an aggregate of the table itself. for each transaction the previous 7 days will look different.

Re: 'ORA-30487: ORDER BY not allowed here' [message #291041 is a reply to message #291034] Wed, 02 January 2008 16:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Since you cannot combine all of the features that you want, I think you will have to settle for a subquery:

SCOTT@orcl_11g> select a.*,
  2  	    (select count (distinct b.custid)
  3  	     from   sampletable b
  4  	     where  b.merchid = a.merchid
  5  	     and    b.txn_date between
  6  		    a.txn_date - 7 and a.txn_date) numcustomers7days
  7  from   sampletable a
  8  order  by a.merchid, a.txn_date
  9  /

     TXNID     CUSTID    MERCHID TXN_DATE  NUMCUSTOMERS7DAYS
---------- ---------- ---------- --------- -----------------
         7          2          1 28-DEC-06                 1
         6          2          1 28-DEC-07                 1
         5          4          1 29-DEC-07                 2
         4          3          1 30-DEC-07                 3
         2          1          1 31-DEC-07                 4
         3          2          1 31-DEC-07                 4
         1          1          1 01-JAN-08                 4

7 rows selected.

SCOTT@orcl_11g> 

Re: 'ORA-30487: ORDER BY not allowed here' [message #291044 is a reply to message #291041] Wed, 02 January 2008 17:19 Go to previous messageGo to next message
tekdoubts
Messages: 4
Registered: January 2008
Junior Member
Since you cannot combine all of the features that you want, I think you will have to settle for a subquery:

Thanks Barbara, the problem is I have a huge table (and each merchant typically has lots of transactions, so the join would also be difficult to pull off) and to top it of I need to do the same thing for 7 days, 1 month, 3 months etc. so it would be incredibly useful if there is a way to utilize the power of analytic functions.

If not, is there some way I can use PL/SQL to do something like this (I've not written even a simple PL/SQL code ever, so I don't it would be better or not than normal SQL for this type of situations)

Re: 'ORA-30487: ORDER BY not allowed here' [message #291567 is a reply to message #291044] Fri, 04 January 2008 23:45 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
tekdoubts wrote on Wed, 02 January 2008 15:19

Since you cannot combine all of the features that you want, I think you will have to settle for a subquery:

Thanks Barbara, the problem is I have a huge table (and each merchant typically has lots of transactions, so the join would also be difficult to pull off) and to top it of I need to do the same thing for 7 days, 1 month, 3 months etc. so it would be incredibly useful if there is a way to utilize the power of analytic functions.

If not, is there some way I can use PL/SQL to do something like this (I've not written even a simple PL/SQL code ever, so I don't it would be better or not than normal SQL for this type of situations)




Oracle handles joins on large tables quite well, as long as you have indexes on the columns that are joined on (merchid and txn_date) and current statistics. You can easily extend the functionality for a month or 3 months or whatever. You cannot use the analytic functions with both the distinct and range features. Any PL/SQL would just duplicate what the sub-query does and would be slower. I have provided a couple of options below. The results are the same only because there is only one week of data.

SCOTT@orcl_11g> select a.*,
  2  	    (select count (distinct b.custid)
  3  	     from   sampletable b
  4  	     where  b.merchid = a.merchid
  5  	     and    b.txn_date between
  6  		    a.txn_date - 7 and a.txn_date) numcustomers7days,
  7  	    (select count (distinct c.custid)
  8  	     from   sampletable c
  9  	     where  c.merchid = a.merchid
 10  	     and    c.txn_date between
 11  		    add_months (a.txn_date, -1) and a.txn_date) numcustomers1month,
 12  	    (select count (distinct d.custid)
 13  	     from   sampletable d
 14  	     where  d.merchid = a.merchid
 15  	     and    d.txn_date between
 16  		    add_months (a.txn_date, -3) and a.txn_date) numcustomers3months
 17  from   sampletable a
 18  order  by a.merchid, a.txn_date
 19  /

     TXNID     CUSTID    MERCHID TXN_DATE  NUMCUSTOMERS7DAYS NUMCUSTOMERS1MONTH NUMCUSTOMERS3MONTHS
---------- ---------- ---------- --------- ----------------- ------------------ -------------------
         7          2          1 28-DEC-06                 1                  1                   1
         6          2          1 28-DEC-07                 1                  1                   1
         5          4          1 29-DEC-07                 2                  2                   2
         4          3          1 30-DEC-07                 3                  3                   3
         2          1          1 31-DEC-07                 4                  4                   4
         3          2          1 31-DEC-07                 4                  4                   4
         1          1          1 01-JAN-08                 4                  4                   4

7 rows selected.



SCOTT@orcl_11g> select a.*,
  2  	    count (distinct b.custid) numcustomers7days,
  3  	    count (distinct c.custid) numcustomers1month,
  4  	    count (distinct d.custid) numcustomers3months
  5  from   sampletable a, sampletable b, sampletable c, sampletable d
  6  where  a.merchid = b.merchid
  7  and    a.merchid = c.merchid
  8  and    a.merchid = d.merchid
  9  and    b.txn_date between a.txn_date - 7 and a.txn_date
 10  and    c.txn_date between add_months (a.txn_date, -1) and a.txn_date
 11  and    d.txn_date between add_months (a.txn_date, -3) and a.txn_date
 12  group  by a.txnid, a.custid, a.merchid, a.txn_date
 13  order  by a.merchid, a.txn_date
 14  /

     TXNID     CUSTID    MERCHID TXN_DATE  NUMCUSTOMERS7DAYS NUMCUSTOMERS1MONTH NUMCUSTOMERS3MONTHS
---------- ---------- ---------- --------- ----------------- ------------------ -------------------
         7          2          1 28-DEC-06                 1                  1                   1
         6          2          1 28-DEC-07                 1                  1                   1
         5          4          1 29-DEC-07                 2                  2                   2
         4          3          1 30-DEC-07                 3                  3                   3
         2          1          1 31-DEC-07                 4                  4                   4
         3          2          1 31-DEC-07                 4                  4                   4
         1          1          1 01-JAN-08                 4                  4                   4

7 rows selected.

SCOTT@orcl_11g> 





Previous Topic: Can I create a FK from a schema user to another?
Next Topic: retrieving data from text file using utl_file
Goto Forum:
  


Current Time: Sun Dec 04 12:34:27 CST 2016

Total time taken to generate the page: 0.09546 seconds