SQL Oracle错误:ORA-00907:缺少右括号

Question

Select m.CEAREGA, m.Crotal, rem.IdRexistro, m.IdMostraExt,
to_char(m.DataEntrega, 'DD/MM/YYYY') As Entrada, e.Descricion AS Ensaio,
to_char(rem.DataValidacion, 'DD/MM/YYYY') as DataValidacion, r.Descricion as Resultado, 
to_char(rem.Valor) as Valor, es.Descricion as Especie, tm.Descricion as Mostra,
(select LISTAGG(mo.Descricion, ':::') WITHIN GROUP (order by mo.Descricion)
from motivo_ensaio_mostra mm
join motivo_ensaio mo on mo.CodMotivo=mm.CodMotivo and mm.codLab=mo.codLab
where mm.IdRexistro=rem.IdRexistro and mm.IdMostra=rem.IdMostra and mm.CodLab=rem.CodLab
group by mm.IdRexistro, mm.IdMostra) as Motivo,
(select LISTAGG(trim(remi.NomeDesc || ' ' || remi.PrimeiroApelido || ' ' || remi.SegundoApelido), ':::') 
WITHIN GROUP (order by remi.PrimeiroApelido, remi.SegundoApelido, remi.NomeDesc)
from rexistro_remitente rm
join remitente remi on remi.NIFCIF=rm.NIFCIF and rm.codLab=remi.codLab
where rm.IdRexistro=rem.IdRexistro and rm.CodLab=rem.CodLab
group by rm.IdRexistro) as Remitente
from resultado_ensaio_mostra rem
join resultado r on r.CodResult=rem.CodResult and r.codLab = rem.codLab and r.CODTIPORESULT='P'
join mostra m on m.IdRexistro=rem.IdRexistro and m.IdMostra=rem.IdMostra and m.codLab = rem.codLab 
and m.VlxBaixa=0 and m.EstadoMostra<>10330004 and LENGTH(m.Crotal<5) and m.IdMostra > 201800000
join especie es on es.CodEspec=m.CodEspec and es.codLab = m.codLab
join tipo_mostra tm on tm.CodTipoMost=m.CodTipoMost and tm.codLab = m.codLab

它显示Oracle错误：ORA-00907

我可以找到缺失的括号或错误在哪里。有人可以帮我吗？

预先感谢。

Answer 1

更改格式样式可能会有所帮助。

对于子征服，我试图使彼此之间的开放和关闭括号保持联系。我倾向于将它们保存在同一列中，以及子查询缩进的内容。

同样，使每个谓词或计算清楚地相互分开。我更喜欢将它们放在单独的行上，它可以使代码更长，但代码较窄。对于diff (以及git(等工具来说，这要友好得多。。

这给了我以下内容，我可以在视觉上比您的示例更快地调试。(实际上，我说我不能在视觉上调试您，每个单独的行或表达都难以隔离和解析。(

SELECT
  m.CEAREGA,
  m.Crotal,
  rem.IdRexistro,
  m.IdMostraExt,
  to_char(m.DataEntrega, 'DD/MM/YYYY') As Entrada,
  e.Descricion AS Ensaio,
  to_char(rem.DataValidacion, 'DD/MM/YYYY') as DataValidacion,
  r.Descricion as Resultado, 
  to_char(rem.Valor) as Valor,
  es.Descricion as Especie,
  tm.Descricion as Mostra,
  (
    select
      LISTAGG(mo.Descricion, ':::')
        WITHIN GROUP (order by mo.Descricion)
    from
      motivo_ensaio_mostra mm
    join
      motivo_ensaio mo
        on  mo.CodMotivo=mm.CodMotivo
        and mm.codLab=mo.codLab
    where
          mm.IdRexistro=rem.IdRexistro
      and mm.IdMostra=rem.IdMostra
      and mm.CodLab=rem.CodLab
    group by
      mm.IdRexistro, mm.IdMostra
  ) as Motivo,
  (
    select
      LISTAGG(trim(remi.NomeDesc || ' ' || remi.PrimeiroApelido || ' ' || remi.SegundoApelido), ':::') 
        WITHIN GROUP (order by remi.PrimeiroApelido, remi.SegundoApelido, remi.NomeDesc)
    from
      rexistro_remitente rm
    join
      remitente remi
        on  remi.NIFCIF=rm.NIFCIF
        and rm.codLab=remi.codLab
    where
          rm.IdRexistro=rem.IdRexistro
      and rm.CodLab=rem.CodLab
    group by
      rm.IdRexistro
  ) as Remitente
from
  resultado_ensaio_mostra rem
join
  resultado r
    on  r.CodResult = rem.CodResult
    and r.codLab = rem.codLab
    and r.CODTIPORESULT='P'
join
  mostra m
    on  m.IdRexistro = rem.IdRexistro
    and m.IdMostra = rem.IdMostra
    and m.codLab = rem.codLab 
    and m.VlxBaixa=0
    and m.EstadoMostra<>10330004
    and LENGTH(m.Crotal<5)
    and m.IdMostra > 201800000
join
  especie es
    on  es.CodEspec=m.CodEspec
    and es.codLab = m.codLab
join
  tipo_mostra tm
    on  tm.CodTipoMost=m.CodTipoMost
    and tm.codLab = m.codLab

这使我得出结论，即括号不是问题。因此，这很可能是括号附近的其他语法错误。

根据我重新格式化您的代码时出现的答案，似乎是LENGTH(m.Crotal<5)，应该是LENGTH(m.Crotal) < 5？

(本质上，<之前缺少)，在5 ...之后也有一个额外的存在...(

Answer 2

什么丑陋的代码；尝试习惯格式化它，以这种方式维护更容易。

无论如何：错误来自AND LENGTH (m.Crotal < 5)行。