ORA-00936缺失表达式：PostgreSQL迁移Oracle查询报错求助

问题原因 #

1. CTE名称拼写不一致
   第一个公共表表达式定义为FILTRADO_INICIAL，但第二个CTE的FROM子句中误写为FILTER_INITIAL，Oracle无法识别该未定义的CTE，直接触发语法错误。
2. Oracle对SELECT *的语法限制
   在Oracle中，当SELECT子句同时包含自定义计算字段和全表字段时，不能直接使用*，必须通过表/CTE别名来限定全表字段（如FILTRADO_INICIAL.*），否则会报"缺失表达式"错误。

修正后的Oracle兼容SQL #

WITH FILTRADO_INICIAL AS (
    SELECT *
      FROM SUREM.tabs_values tv
     WHERE tv.affected = 1
       AND tv.incidents = 1
       AND tv.tab = 12 
),
LAST_DATA AS (
    SELECT CASE
             WHEN idrow IS NULL THEN ROW_NUMBER() OVER (PARTITION BY affected, incidents, field, owner ORDER BY insert_date DESC)
             ELSE 1
           END AS counter_field,
           CASE
             WHEN idrow IS NULL THEN NULL
             ELSE ROW_NUMBER() OVER (PARTITION BY affected, incidents, field, owner, idrow ORDER BY insert_date DESC)
           END AS counter_row,
           FILTRADO_INICIAL.*  -- 用CTE别名限定全表字段
      FROM FILTRADO_INICIAL  -- 修正CTE名称拼写
)
SELECT *
  FROM LAST_DATA
 WHERE (counter_field = 1 AND counter_row IS NULL)
    OR (counter_field = 1 AND counter_row = 1)

内容的提问来源于stack exchange，提问作者Inma Martín