SQL Oracle Error: ORA-00907: missing right parenthesis

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

It shows the Oracle Error: ORA-00907

I can´t find the missing parenthesis or where is the error. Could anyone can help me out?

Thanks in advance.

Answer 1

Changing your formatting style may help.

For sub-queries, I try to keep the open and close brackets visibly associated with each other. I tend to keep them in the same column, and the content of the sub-query indented.

Similarly, make each predicate or calculation clearly separate from each other. I prefer to have them on separate lines, it makes for longer code, but narrower code; which is much more friendly for tools such as diff (and so also git).

This gives me the following which I can visually debug much faster than your example. (In fact, I'd say that I can't visually debug yours, each individual line or expression is too hard to isolate and parse.)

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

This leads me to the conclusion that the brackets are not the problem. So, it's likely to be some other syntax error near a bracket.

As per an answer that came up while I was reformatting your code, it appears to be LENGTH(m.Crotal<5) which should be LENGTH(m.Crotal) < 5?

(In essence, there is a ) missing before the <, and also an extra one present after the 5...)