Different Value - Oracle Query

Asked

Viewed 163 times

0

In the query below in the column Valor_Total_da_Ordem is giving a totally different value, far above what was actually to be. The value is giving 55990.62. The correct value being 1333.11.

What may have occurred?

SELECT DISTINCT
    e.nm_fantasia_estab Unidade,
    m.nr_ordem_compra Número_Ordem_Compra,
    l.cd_cgc CNPJ_Fornecedor,
    l.ds_razao_social Razão_Social_Fornecedor,
    sum(v.vl_vencimento) Valor_Total_da_Ordem,
    substr(
        tasy.obter_nome_pf (p.cd_pessoa_fisica),
        1,
        255
    ) Aprovador,
    trunc (m.dt_aprovacao) Data_Aprovacao
FROM
    TASY.PROCESSO_APROV_COMPRA p,
    tasy.PROCESSO_COMPRA c,
    TASY.ORDEM_COMPRA_ITEM t,
    tasy.ORDEM_COMPRA m,
    TASY.ESTABELECIMENTO e,
    TASY.pessoa_juridica l,
    TASY.ORDEM_COMPRA_VENC v
WHERE
    p.NR_SEQUENCIA = c.NR_SEQUENCIA
AND t.NR_ORDEM_COMPRA = m.NR_ORDEM_COMPRA
AND e.cd_estabelecimento = m.cd_estabelecimento
AND l.cd_cgc = m.cd_cgc_fornecedor
AND v.nr_ordem_compra = m.nr_ordem_compra
AND m.nr_ordem_compra = 29020
AND p.nr_sequencia IN (
    SELECT DISTINCT
        nr_seq_aprovacao
    FROM
        tasy.ordem_compra_item
    WHERE
        nr_ordem_compra = 29020
)
GROUP BY
    e.nm_fantasia_estab,
    m.nr_ordem_compra,
    c.nr_sequencia,
    l.cd_cgc,
    l.ds_razao_social,
    trunc (m.dt_aprovacao),
    m.dt_aprovacao,
    substr(
        tasy.obter_nome_pf (p.cd_pessoa_fisica),
        1,
        255
    )
ORDER BY
    m.nr_ordem_compra DESC;
  • Not repeating values? Try adding a DISTINCT and running your select again.

  • 1

    Just with select do not tell you the values that should be returned or not, however, there is a CROSS JOIN in your query that is possibly returning more records than it should and popping the total value.

  • @Laerte, I did it... but he gives a lower value. Because in the table he has 3 values. 444,34 444,34 444,43 .

  • I seem to be missing a Join among some table, you have 7 tables in the from and only 5 joins in the Where. Check your joins

  • You relate P=C but do not relate any of these two tables to others from your, without knowing the modeling of your bank it is difficult to identify which JOIN is missing.

  • Pablotondolodevargas, it worked. I adjusted the joins here. Thank you

  • 1

    @renanbessa, since you solved your problem, please answer your question yourself explaining how you solved, maybe serve to help another user going through the same problem.

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.