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.
– Laerte
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.
– RXSD
@Laerte, I did it... but he gives a lower value. Because in the table he has 3 values. 444,34 444,34 444,43 .
– renan bessa
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
– Pablo Tondolo de Vargas
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.
– Pablo Tondolo de Vargas
Pablotondolodevargas, it worked. I adjusted the joins here. Thank you
– renan bessa
@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.
– Laércio Lopes