0
Good afternoon,
I have a table SB2010 where I have the current quantity of product in stock and have the table SB8010 where I have the quantity of the product separated by lots. I need to verify if there is any divergence between the current quantity and the sum of the quantity in the batch table. I created a script, but data appears where even equal, is accused as different. Would anyone have any idea how to solve?
SELECT B8_FILIAL,
B8_PRODUTO,
B8_LOCAL,
SUM(B8_SALDO) AS Total_B8,
B2_QATU FROM SB8010 AS SB8
INNER JOIN SB2010 AS SB2
ON (SB8.B8_FILIAL = SB2.B2_FILIAL
AND SB8.B8_PRODUTO = SB2.B2_COD
AND SB8.B8_LOCAL = SB2.B2_LOCAL)
WHERE SB8.D_E_L_E_T_ <> '*'
AND SB2.D_E_L_E_T_ <> '*'
GROUP BY B8_FILIAL, B8_PRODUTO, B8_LOCAL, B2_QATU
HAVING B2_QATU <> SUM(B8_SALDO);
In this case, as observed, the above query should bring 0 records, since there are no divergences between the values.
Obs: Field Total_sb8 is the sum of the quantity per batch of the product (in this case, no matter the lots, only the sum of the total quantity) Field B2_QATU is the current product quantity
HAVING B2_QATU <> Total_b8;
– Marcos Marques
@Marcosmarques this way ends up giving invalid column.
– Thiago Silva
Weird, tries to transform the 2, see if it works. HAVING cast(sum(B2_QATU) as Numeric(9,4)) <> cast(sum(Total_b8) as Numeric(9,4))
– Marcos Marques
How is the typing (decimal places) of these fields ? Strange too.
– Motta
@Marcosmarques Arithmetic overflow error when converting float to Numeric data type.
– Thiago Silva
@Motta both are Float
– Thiago Silva
HAVING cast(sum(B2_QATU) as float) <> cast(sum(Total_b8) as float). Try to put the 2 to float then. Just to ensure the integrity of the types. I was very curious about your case.
– Marcos Marques
@Marcosmarques Brought several records with the 2 equal columns. It was with 4 records (as print sent) and with this command was 377 records
– Thiago Silva
Having no access to the base is complicated, sorry. But try now. HAVING sum(cast(B2_QATU as float )) <> cast(sum(Total_b8) as float). Placing the transfer in the column before applying the aggregate function.
– Marcos Marques
Must solve like this
ROUND(B2_QATU,4) <> ROUND(SUM(B8_SALDO),4).– Bruno Warmling
@Brunowarmling It worked out, thank you very much
– Thiago Silva