Using subselect in oracle. Why is the error occurring?

Question

I’m trying to use this code below, but the oracle returns me the following error:

ORA-00907: right parenthesis not found 00907. 00000 - "Missing right parenthesis" *Cause:
*Action: Line error: 30 Column: 10

I can’t identify where the problem is.

select p.empresa as cod_empresa, p.filial as cod_estab, gp.codigo as cod_cliente, td.sigla as esp_docto, fd.serie as serie_docto,
       fd.documentodigitado as num_docto, p.parceladigitada as parcela, p.banco as cod_banco, tc.codigo as cod_carteira, p.numerobanco as nosso_numero,
       p.datainclusao as dt_emissao, p.datavencimento as dt_ven_ori, p.vctoprorrogado as dt_vencimento, p.valor as val_documento, p.valor - p.valoresbaixados as val_saldo,
       p.percentualmora as pct_juros_dia, p.percentualmulta as pct_multa, fd.enviadoserasa as enviado, p.handle as num_tit_unico, 
       ( SELECT CC.ESTRUTURA FROM BENNERCORP.CM_ITENS CI 
         LEFT JOIN BENNERCORP.CT_CC CC ON CC.HANDLE = CI.CENTROCUSTO
         where CI.documento = fd.handle
         and   ROWNUM <= 1
         order by CI.valorliquido desc ) as cod_unid_negoc,
       kr.DSREGIAOCOMERCIAL as cod_canal_venda from bennercorp.fn_parcelas p
    inner join bennercorp.fn_documentos fd on fd.handle = p.documento
                               and fd.ENTRADASAIDA = 'S'
                               AND fd.TIPODEMOVIMENTO IN (1, 2)
                               and fd.ABRANGENCIA <> 'R'
                           AND fd.ABRANGENCIA <> 'F'
                   AND fd.ABRANGENCIA <> 'B'
                   AND fd.ABRANGENCIA <> 'D'
                   AND fd.ABRANGENCIA <> 'G'
                   AND fd.ABRANGENCIA <> 'H'
                               AND fd.documentodigitado NOT like '%CON%'
                               AND fd.documentodigitado NOT like '%CTR%'
                               AND fd.documentodigitado NOT like '%FQF%'
                               AND UPPER(fd.documentodigitado) NOT like '%CONT%'
                               AND fd.documentodigitado NOT like '%FQV%'
    inner join bennercorp.fn_tiposdocumentos td on td.handle = fd.tipodocumento
    inner join bennercorp.gn_pessoas gp on gp.handle = fd.pessoa
    left join bennercorp.fn_tiposcobrancas tc on tc.handle = p.tipocobranca
    inner join bennercorp.empresas e on e.handle = p.empresa
    left join bennercorp.k_regiacao_comercial kr on kr.handle = gp.K_CDUFREGIAOCOMERCIAL
    where p.vctoprorrogado < current_date
    and   p.dataliquidacao is null
    and   p.ehadiantamento = 'N'
    and   p.emaberto = 'S'
    and   p.parcelacancelada = 'N'

Answer 1

Missed closing the parentheses after that:

kr.DSREGIAOCOMERCIAL AS cod_canal_venda

However, you also missed setting a limit, because if your sub query returns more than one line will generate another error, your query would look like this:

SELECT 
  p.empresa AS cod_empresa,
  p.filial AS cod_estab,
  gp.codigo AS cod_cliente,
  td.sigla AS esp_docto,
  fd.serie AS serie_docto,
  fd.documentodigitado AS num_docto,
  p.parceladigitada AS parcela,
  p.banco AS cod_banco,
  tc.codigo AS cod_carteira,
  p.numerobanco AS nosso_numero,
  p.datainclusao AS dt_emissao,
  p.datavencimento AS dt_ven_ori,
  p.vctoprorrogado AS dt_vencimento,
  p.valor AS val_documento,
  p.valor - p.valoresbaixados AS val_saldo,
  p.percentualmora AS pct_juros_dia,
  p.percentualmulta AS pct_multa,
  fd.enviadoserasa AS enviado,
  p.handle AS num_tit_unico,
  (SELECT 
    CC.ESTRUTURA 
  FROM
    BENNERCORP.CM_ITENS CI 
    LEFT JOIN BENNERCORP.CT_CC CC 
      ON CC.HANDLE = CI.CENTROCUSTO 
  WHERE CI.documento = fd.handle 
    AND ROWNUM <= 1 
  ORDER BY CI.valorliquido DESC) AS cod_unid_negoc,
  kr.DSREGIAOCOMERCIAL AS cod_canal_venda 
  LIMIT 1) 
FROM
  bennercorp.fn_parcelas p 
  INNER JOIN bennercorp.fn_documentos fd 
    ON fd.handle = p.documento 
    AND fd.ENTRADASAIDA = 'S' 
    AND fd.TIPODEMOVIMENTO IN (1, 2) 
    AND fd.ABRANGENCIA <> 'R' 
    AND fd.ABRANGENCIA <> 'F' 
    AND fd.ABRANGENCIA <> 'B' 
    AND fd.ABRANGENCIA <> 'D' 
    AND fd.ABRANGENCIA <> 'G' 
    AND fd.ABRANGENCIA <> 'H' 
    AND fd.documentodigitado NOT LIKE '%CON%' 
    AND fd.documentodigitado NOT LIKE '%CTR%' 
    AND fd.documentodigitado NOT LIKE '%FQF%' 
    AND UPPER(fd.documentodigitado) NOT LIKE '%CONT%' 
    AND fd.documentodigitado NOT LIKE '%FQV%' 
  INNER JOIN bennercorp.fn_tiposdocumentos td 
    ON td.handle = fd.tipodocumento 
  INNER JOIN bennercorp.gn_pessoas gp 
    ON gp.handle = fd.pessoa 
  LEFT JOIN bennercorp.fn_tiposcobrancas tc 
    ON tc.handle = p.tipocobranca 
  INNER JOIN bennercorp.empresas e 
    ON e.handle = p.empresa 
  LEFT JOIN bennercorp.k_regiacao_comercial kr 
    ON kr.handle = gp.K_CDUFREGIAOCOMERCIAL 
WHERE p.vctoprorrogado < CURRENT_DATE 
  AND p.dataliquidacao IS NULL 
  AND p.ehadiantamento = 'N' 
  AND p.emaberto = 'S' 
  AND p.parcelacancelada = 'N' ;