CODEWITHSUNDEEP
sqloracle-databasedenodo
KSp
KSp

Reputation: 1239

month function is not created error on the denodo server using oracle database

I am trying to show the delta for total (current vs previous day) but the month function does not work in oracle. I also use denodo to run this query. I tried to add an extract function to make it work with the month but also does not seem to work fine.

pedics :

study id        date         total
RSCLS CA10001  2020-08-11    52
RSCLS CA10001  2020-08-10    52
ETDLD CA20302  2020-08-11    99
ERGKG CA34524  2020-08-11    31

Query:

select
  tt1.study,
  tt1.id,
  tt1.date,
  tt1.total,
  (tt1.total-ifnull(tt2.total, 0)) as delta
from pedics tt1
  left outer JOIN pedics tt2 on tt1.total = tt2.total
    and month(tt1.date1)-month(tt2.date1)=1;

Upvotes: 0

Views: 568

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Do not use month() or extract() for this! It is not going to work in January. Instead:

select tt1.study, tt1.id, tt1.date, tt1.total,
       (tt1.total - coalesce(tt2.total, 0)) as delta
from pedics tt1 left outer join
     pedics tt2
     on tt1.total = tt2.total and
        trunc(tt1.date1, 'MON') = trunc(tt2.date1, 'MON') + interval '1' month;

However, your question suggests that you just want the previous row based on the date. So, I would imagine that you really want:

select p.*,
       (p.total - lag(p.total, 1, 0) over (partition by study_id order by date)) as delta
from pedics p;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try the below - using extract(month from datecolumn)

select
  tt1.study,
  tt1.id,
  tt1.date,
  tt1.total,
  tt1.total-coalesce(tt2.total, 0) as delta
from pedics tt1
  left outer JOIN pedics tt2 on tt1.total = tt2.total
    and extract(month from tt1.date)-extract(month from tt2.date)=1

Upvotes: 0

Related Questions