Sql query join gives invalid parameter types error

Question

I am trying to transform the query I have by changing the join to be on study and id and date<->date -1 (on one table the normal data and in the other table the date- 1), but I am making some mistake which gives me an error.

Database: Oracle and running on Denodo Server

pedics

study id        cal_dtm    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.cal_dtm,
  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.cal_dtm)-extract(month from tt2.cal_dtm)=1

Query with the condition needed which throws an error:

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

Error: Error in join view conditions: Invalid parameter types of function '-(tt2.cal_dtm, '1')'

Answer 1

If you are looking for the delta between the day in the row and the prior day, it is much more efficient to use the LAG analytic function. This way you are able to just look at the prior row's "total" and find the difference between the current row's total.

Setup

I added some more sample data than what you had provided to show more cases.

CREATE TABLE pedics
(
    study      VARCHAR2 (5),
    id         VARCHAR2 (7),
    cal_dtm    DATE,
    total      NUMBER
);

INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('RSCLS', 'CA10001', DATE '2020-08-11', 52);
INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('RSCLS', 'CA10001', DATE '2020-08-10', 52);
INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('RSCLS', 'CA10001', DATE '2020-08-09', 50);
INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('ETDLD', 'CA20302', DATE '2020-08-11', 99);
INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('ERGKG', 'CA34524', DATE '2020-08-11', 31);
INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('ERGKG', 'CA34524', DATE '2020-08-12', 35);
INSERT INTO pedics (study, id, cal_dtm, total) VALUES ('ERGKG', 'CA34524', DATE '2020-08-13', 26);

Query

  SELECT p.*, total - LAG (total) OVER (PARTITION BY study, id ORDER BY cal_dtm) AS delta
    FROM pedics p
ORDER BY study, id, cal_dtm;

Results

STUDY | ID      | CAL_DTM   | TOTAL | DELTA
--------------------------------------------
ERGKG | CA34524 | 8/11/2020 |    31 | 
ERGKG | CA34524 | 8/12/2020 |    35 | 4
ERGKG | CA34524 | 8/13/2020 |    26 | -9
ETDLD | CA20302 | 8/11/2020 |    99 | 
RSCLS | CA10001 | 8/9/2020  |    50 | 
RSCLS | CA10001 | 8/10/2020 |    52 | 2
RSCLS | CA10001 | 8/11/2020 |    52 | 0