oracle ORA-00936：falta una expresión [duplicate]

mjqavswn 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(111)

此问题已在此处有答案：

How do I select all the columns from a table, plus additional columns like ROWNUM?（4个答案）
20天前关闭。
我在Oracle中执行我在Postgres中创建的以下查询：

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,
       *
  from FILTER_INITIAL
)
select *
  from LAST_DATA
 where (counter_field = 1 and counter_row is null)
    or (counter_field = 1 and counter_row = 1)

然而，在Oracle中，它对我来说并不正确，告诉我我丢失了一个以* from FILTRADO_INICIAL开头的表达式。有人知道问题出在哪里吗？
太感谢了
希望有人知道答案：）

oracle

来源：https://stackoverflow.com/questions/77171619/ora-00936-falta-una-expresi%c3%b3n

1条答案

按热度按时间

csga3l581#

在Oracle中，您需要用表名或别名 * 限定 * 星号：

END AS contador_row,
              FILTRADO_INICIAL.*                               --> here
         FROM FILTRADO_INICIAL)

完整代码：

WITH
   FILTRADO_INICIAL
   AS
      (SELECT *
         FROM SUREM.tabs_values tv
        WHERE     tv.afectado = 1
              AND tv.incidentes = 1
              AND tv.tab = 12),
   LAST_DATA
   AS
      (SELECT CASE
                 WHEN idrow IS NULL
                 THEN
                    ROW_NUMBER ()
                       OVER (PARTITION BY afectado,
                                          incidentes,
                                          field,
                                          owner
                             ORDER BY insert_date DESC)
                 ELSE
                    1
              END AS contador_field,
              CASE
                 WHEN idrow IS NULL
                 THEN
                    NULL
                 ELSE
                    ROW_NUMBER ()
                       OVER (PARTITION BY afectado,
                                          incidentes,
                                          field,
                                          owner,
                                          idrow
                             ORDER BY insert_date DESC)
              END AS contador_row,
              FILTRADO_INICIAL.*                               --> here
         FROM FILTRADO_INICIAL)
SELECT *
  FROM LAST_DATA
 WHERE    (    contador_field = 1
           AND contador_row IS NULL)
       OR (    contador_field = 1
           AND contador_row = 1)

赞(0）回复(0）举报 2023-10-16

我来回答

oracle ORA-00936：falta una expresión [duplicate]

1条答案

相关问题

热门标签

最新问答