ora-00933 sql command not properly ended INNER JOIN - SUBQUERY -DATE

Question

my Query ended with the Error "ora-00933 sql command not properly ended"

The Query:

create table tmp_cleaner
as (
SELECT  dd.bigtriggerid
FROM documentdata dd
inner join ipl_bigtrigger bt on dd.bigtriggerid = bt.triggerid
inner join documentinfo di on dd.bigtriggerid = di.bigtriggerid
where bt.processed = 'T'
and di.status in (1, 2)
and bt.processdatetime <= add_months(sysdate, -3));

Delete  FROM documentdata  where exists (select bigtriggerid from tmp_cleaner where bigtriggerid = documentdata.bigtriggerid);

Error in Line 9. (last line)
ora-00933 sql command not properly ended 

Where is my mistake? should i use trunc with date?

I tried to put the Join-Operators in ()

Answer 1

There's nothing wrong with that code.

Sample tables:

SQL> create table documentdata as select 1 bigtriggerid from dual;

Table created.

SQL> create table ipl_bigtrigger as select 1 triggerid, 'T' processed, date '2022-02-25' processdatetime from dual;

Table created.

SQL> create table documentinfo as select 1 bigtriggerid, 1 status from dual;

Table created.

Your query, as is:

SQL> CREATE TABLE tmp_cleaner
  2  AS
  3     (SELECT dd.bigtriggerid
  4        FROM documentdata dd
  5             INNER JOIN ipl_bigtrigger bt ON dd.bigtriggerid = bt.triggerid
  6             INNER JOIN documentinfo di ON dd.bigtriggerid = di.bigtriggerid
  7       WHERE     bt.processed = 'T'
  8             AND di.status IN (1, 2)
  9             AND bt.processdatetime <= ADD_MONTHS (SYSDATE, -3));

Table created.

SQL> select * from tmp_cleaner;

BIGTRIGGERID
------------
           1

SQL>

Could you provide some context? Where did you run that piece of code?

---

There's nothing wrong with delete either (apart from the fact that you're using a new table in the subquery; shouldn't that be tmp_cleaner you've just created?):

SQL> Delete  FROM documentdata
  2    where exists (select bigtriggerid
  3                  from tmp_cleaner -- tmp_loeschbigtrigger
  4                  where bigtriggerid = documentdata.bigtriggerid);

1 row deleted.

SQL>