Fw: [firebase-br] Dúvida com SQL

[Ricardo Malikoski]

     Pessoal tive problemas com a SQL mais abaixo, e resolvi graças ao colega Augusto, colocando no GROUP BY os campos das condições das SUBSELECTS. 

    Valeu a todos


  ----- Original Message ----- 
  From: Ricardo Malikoski 
  To: Augusto 
  Sent: Thursday, September 22, 2005 4:21 PM
  Subject: Re: [firebase-br] Dúvida com SQL


  Valeu Augusto, funcionou, jamais iria imaginar em colocar no GROUP BY as cláusulas do WHERE das subselects.

  Obrigado mesmo.

  Ricardo Malikoski
    ----- Original Message ----- 
    From: Augusto 
    To: Ricardo Malikoski 
    Sent: Thursday, September 22, 2005 2:47 PM
    Subject: Re: [firebase-br] Dúvida com SQL


    Segue outra dica,

    inclua os campo do where do subselect no group by externo.

    NVV.FIL_CODIGO, NVV.SER_CODIGO, NVV.NF_NUMERO

    teste aquery abaixo.

    SELECT
    NV.OPFIS_CODIGO,
    SUM(NV.NF_VLRNOTA) AS TOTCONTABIL, SUM(NVV.NF_VLRBASEICMS7) AS BASE7,
    SUM(NVV.NF_VLRBASEICMS12) AS BASE12, SUM(NVV.NF_VLRBASEICMS17) AS BASE18,
    SUM(NVV.NF_VLRBASEICMS25) AS BASE25, SUM(NVV.NF_VLRBASEICMS27) AS BASE27,
    SUM(NVV.NF_VLRICMS7) AS VLR7, SUM(NVV.NF_VLRICMS12) AS VLR12,
    SUM(NVV.NF_VLRICMS17) AS VLR18, SUM(NVV.NF_VLRICMS25) AS VLR25,
    SUM(NVV.NF_VLRICMS27) AS VLR27,
    (SELECT SUM(NVV.NF_BASEICMSX)
    FROM NOTASVENDA_VALORES NVV2
    WHERE NVV2.FIL_CODIGO = NVV.FIL_CODIGO AND
    NVV2.SER_CODIGO = NVV.SER_CODIGO AND
    NVV2.NF_NUMERO = NVV.NF_NUMERO AND
    NVV2.NF_ALIQICMSX = 17 ) AS BASE17,
    (SELECT SUM(NVV.NF_BASEICMSX)
    FROM NOTASVENDA_VALORES NVV2
    WHERE NVV2.FIL_CODIGO = NVV.FIL_CODIGO AND
    NVV2.SER_CODIGO = NVV.SER_CODIGO AND
    NVV2.NF_NUMERO = NVV.NF_NUMERO AND
    NVV2.NF_ALIQICMSX = 26 ) AS BASE26,
    (SELECT SUM(NVV.NF_VLRICMSX)
    FROM NOTASVENDA_VALORES NVV2
    WHERE NVV2.FIL_CODIGO = NVV.FIL_CODIGO AND
    NVV2.SER_CODIGO = NVV.SER_CODIGO AND
    NVV2.NF_NUMERO = NVV.NF_NUMERO AND
    NVV2.NF_ALIQICMSX = 17 ) AS VLR17,
    (SELECT SUM(NVV.NF_VLRICMSX)
    FROM NOTASVENDA_VALORES NVV2
    WHERE NVV2.FIL_CODIGO = NVV.FIL_CODIGO AND
    NVV2.SER_CODIGO = NVV.SER_CODIGO AND
    NVV2.NF_NUMERO = NVV.NF_NUMERO AND
    NVV2.NF_ALIQICMSX = 26 ) AS VLR26,
    SUM(NVV.NF_VLRISENTASICMS) AS ISENTAS, SUM(NVV.NF_VLROUTRASICMS ) AS OUTRAS,
    SUM(NVV.NF_VLRBASEIPI) AS BASEIPI, SUM(NVV.NF_VLRIPI) AS VLRIPI,
    SUM(NVV.NF_BASEICMSFRETE) AS BASEFRETE, SUM(NVV.NF_VLRICMSFRETE) AS VLRICMSFRETE,
    SUM(NV.NF_VLRFRETE) AS VLRFRETE
    FROM
    (
    (NOTASVENDA NV
    INNER JOIN NOTASVENDA_VALORES NVV ON NV.FIL_CODIGO = NVV.FIL_CODIGO AND
    NV.SER_CODIGO = NVV.SER_CODIGO AND
    NV.NF_NUMERO = NVV.NF_NUMERO)
    LEFT JOIN PARAMETROSCONTABEIS PC ON NV.OPFIS_CODIGO = PC.OPFIS_CODIGO AND
    NV.ORFIS_CODIGO = PC.ORFIS_CODIGO
    )
    WHERE
    NV.NF_SITUACAO = 'A' AND
    ( (NV.OPFIS_CODIGO BETWEEN 500 AND 999) OR (NV.OPFIS_CODIGO > 4999) ) AND
    NV.FIL_CODIGO = 1 AND
    NV.SER_CODIGO IN ('U') AND
    NV.NF_DATALCTO BETWEEN '01/01/2005' AND '12/31/2006'
    GROUP BY
    NV.OPFIS_CODIGO, NVV.FIL_CODIGO, NVV.SER_CODIGO, NVV.NF_NUMERO


    Augusto  []'s


    ************************************************************************************************************************************************************************