getting error- ORA-00905: missing keyword-CodePudding

Select * from table1 t1 left outer join table2 t2 on t1.id=t2.id and 
case 
when t1.id in (select t2.id from table2)
then t1.valid_to_ts > sysdate and t2.valid_to_ts>sysdate
else
t1.valid_to_ts>sysdate.

getting error-

ORA-00905: missing keyword

CodePudding user response：

You can use UNIONs to implement the logic of the case:

SELECT *
  FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
 WHERE t2.id IS NULL
   AND t1.valid_to_ts > SYSDATE
 UNION ALL
SELECT *
  FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id
 WHERE t1.id IS NOT NULL
   AND t1.valid_to_ts > SYSDATE
   AND t2.valid_to_ts > SYSDATE;

CodePudding user response：

As Álvaro González said in a comment, you can't use a case expression as a flow control operator. You can use a case expression in a where or on clause, but only by making it generate a value which you then compare with something else, which can be awkward, so it's usually better not to. You can usually replace the logic you're trying to implement with simple Boolean logic.

In this case you're already joining to table2 so your subquery isn't needed; you can use a where clause to see if a matching record was found - preferably checking for a not-null column other than ID, so this example will only work if valid_to_ts is not nullable:

select * from table1 t1
left outer join table2 t2
on t1.id = t2.id
where t1.valid_to_ts > sysdate
and (t2.valid_to_ts is null or t2.valid_to_ts > sysdate)

If t2.valid_to_ts is nullable then you should use a different not-nullable column instead; unless you want to include values with no valid-to date - but you aren't doing that for t1.valid_to_ts.

If you try to check that in the on clause then you won't filter out IDs which do exist in table2 but with an earlier date.

db<>fiddle, including an on-clause version which gets the wrong result (as far as I can tell from your starting query anyway).

For the demo I've assumed that the _ts columns are dates, since you're comparing with sysdate; if they are actually timestamps (as the suffix might suggest) then you could compare with systimestamp instead.