oracle db from keyword not found where expected in double cte-CodePudding

I have a double cte expression , the first one join two tables and the second is implementing a partition by function:

with cte as (
  select * 
  from memuat.product p
  join memuat.licence l on p.id = l.product_id
  where l.managed = 'TRUE'
),
joined as (
  select
    *,
    row_number() over (partition by id order by id) as rn
  from cte
)
select * from joined;

I get the following error:

ORA-00923: FROM keyword not found where expected, ERROR at line 12. I cannot figure out which syntax error is wrong in my query.

CodePudding user response：

Oracle is nitpicking when it comes to SELECT *. SELECT * means "select everything", so how can you possibly add something to it? In Oracle you cannot SELECT *, 1 AS something_else FROM some_table. You must have SELECT some_table.*, 1 AS something_else FROM some_table, so you are no longer selecting "everything", but "everything from the table" :-)

You have

select
  *,
  row_number() over (partition by id order by id) as rn
from cte

It must be

select
  cte.*,
  row_number() over (partition by id order by id) as rn
from cte

instead.