Bill Brasky - 1 year ago 89

SQL Question

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)`

over(partition by th.customer_id

order by th.trx_datetime

range between 1 preceding and 0 following) as rolling_trx_amt

from transactions th;

Now when I run the query I get the following error.

`ORA-00902: invalid datatype`

00902. 00000 - "invalid datatype"

I've searched for hours to find a solution and tried countless conversions on th.trx_datetime but haven't been able to find a way to correct the error. If you know how to get the analytics function order by statement to work with a time stamp please let me know.

Answer Source

You're getting that error because your range is using integers (which works fine with date arithmetic, as that is calculated in numbers of days), whereas timestamp arithmetic uses intervals.

Therefore you need to convert your ranges into intervals, which you can do using `numtodsinterval`

, like so:

```
select sum(th.amount)
over(partition by th.customer_id
order by th.trx_datetime
range between numtodsinterval(1, 'DAY') preceding
and numtodsinterval(0, 'DAY') following) as rolling_trx_amt
from transactions th;
```

You could also rewrite this as:

```
select sum(th.amount)
over(partition by th.customer_id
order by th.trx_datetime
range between numtodsinterval(1, 'DAY') preceding
and current row) as rolling_trx_amt
from transactions th;
```

because when you use a windowing clause with range, "current row" equates to "rows with the same value as the current row".