sqloracle-databasedenodo

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;
Solution

  • 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