CODEWITHSUNDEEP
sqloracle10g
Yesmine Slim
Yesmine Slim

Reputation: 3

ORA-00923 error: FROM keyword not found where expected

When calculating retention on Oracle DB, I wrote this code:

select 
sessions.sessionDate , 
count(distinct sessions.visitorIdd) as active_users, 
count(distinct futureactivity.visitorIdd) as retained_users,
count(distinct futureactivity.visitorIdd) / count(distinct sessions.visitorIdd)::float as retention
FROM sessions
left join sessions futureactivity on
sessions.visitorIdd=futureactivity.visitorIdd
and sessions.sessionDate = futureactivity.sessionDate - interval '3' day
group by 3;

but I always get the error: "ORA-00923: mot-clé FROM absent à l'emplacement prévu" (ORA-00923 FROM keyword not found where expected) Can you help me guys?

Upvotes: 0

Views: 929

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Here is a more "Oracle" way of writing the query:

select s.sessionDate , 
       count(distinct s.visitorIdd) as active_users, 
       count(distinct fs.visitorIdd) as retained_users,
       count(distinct fs.visitorIdd) / count(distinct s.visitorIdd) as retention
from sessions s left join
     sessions fs
     on s.visitorIdd = fs.visitorIdd and
        s.sessionDate = fs.sessionDate - interval '3' day
group by s.sessionDate
order by s.sessionDate;

Notes:

  • Oracle does not require conversion with dividing integers.
  • The group by should contain the column name, and it is actually "1", not "3".
  • Shorter table aliases make the query easier to write and to read.
  • You'll probably want an order by, because the results will be an in indeterminate order.
  • There is probably a better way to write this query using window functions.

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

Oracle does not recognize :: syntax of Postgres, so it complains of the missing FROM keyword not being found where expected.

Use a cast instead:

count(distinct futureactivity.visitorIdd) / cast(count(distinct sessions.visitorIdd) as float) as retention

Upvotes: 1

Related Questions