ORA-00907:缺少右括号Hrk

Question

我正在尝试在oracle数据库上使用以下代码。

select *
from (
    select 
        LBRCODE,
        PRDACCTID,
        (
            CASE
                WHEN to_char(CBLDATE, 'dd-mon-yyyy') <> '01-jan-2021'
                THEN to_date('01-jan-2021', 'dd-mon-yyyy')
                ELSE CBLDATE
            END
        ) AS CBLDATE,
        BALANCE1,
        BALANCE2,
        BALANCE3,
        BALANCE4,
        rank() over(
            partition by lbrcode, prda cctid order by cbldate desc
        ) rnk
    from cbl 
    WHERE CBLDATE <= '01-jan-2021'
)
where rnk = 1

我收到错误ORA-00907: missing right parenthesis

我在这里错过了什么？

请帮帮忙

Answer 1

RANK分析函数中的prda cctid与您以前在选择列表中使用的prdacctid不匹配；我猜测您有一个需要删除的额外空格的拼写错误。如果它们是两列，那么在它们之间需要一个逗号。

您还可以使用DATE字面量，这样就不需要对字符串进行隐式和显式强制转换：

select *
from   (
         select LBRCODE,
                PRDACCTID,
                (CASE
                 WHEN CBLDATE <  DATE '2021-01-01'
                 OR   CBLDATE >= DATE '2021-01-01' + INTERVAL '1' DAY
                 THEN DATE '2021-01-01'
                 ELSE CBLDATE
                 END
                ) AS CBLDATE,
                BALANCE1,
                BALANCE2,
                BALANCE3,
                BALANCE4,
                rank() over(
                  partition by lbrcode, prdacctid  -- remove the space here
                  order by cbldate desc
                ) rnk
         from   cbl 
         WHERE  CBLDATE <= DATE '2021-01-01'
)
where rnk=1

假设您的WHERE过滤器将过滤掉2021-01-01 00:00:00之后的任何值，那么您可以进一步简化代码以：

select *
from   (
         select LBRCODE,
                PRDACCTID,
                DATE '2021-01-01' AS CBLDATE,
                BALANCE1,
                BALANCE2,
                BALANCE3,
                BALANCE4,
                rank() over(
                  partition by lbrcode, prdacctid  -- remove the space here
                  order by cbldate desc
                ) rnk
         from   cbl 
         WHERE  CBLDATE <= DATE '2021-01-01'
)
where rnk=1