CODEWITHSUNDEEP
sql
Peter O
Peter O

Reputation: 93

SQL Query returns different set of data each time it runs (inconsistent)

Below is my query. When I comment out the last comparison WHERE factuurbedrag <> totaalbedrag_regels the query returns a consistent set of data. When that where statement is active, it is different. What am i doing wrong?

Code:

SELECT * FROM 
    (SELECT     
           faktuur.faknr, SUM(faktuur.bdr_ev_ed_val) AS factuurbedrag,totaalbedrag_regels, bedrijfsnaam, contractnr
    FROM         
       frhsrg AS faktuur 
       INNER JOIN Items ON faktuur.artcode = Items.ItemCode 
       JOIN 
          (SELECT 
              SUM(amutas.bedrag) AS totaalbedrag_regels,amutas.reknr,amutas.faktuurnr AS faknr,cicmpy.cmp_name AS bedrijfsnaam, amutas.project AS contractnr
           FROM 
              [001].[dbo].[amutak] 
              INNER JOIN [amutas] ON amutak.bkstnr = amutas.bkstnr 
              INNER JOIN cicmpy ON amutas.debnr = cicmpy.debnr
           WHERE 
              amutak.dagbknr = 90 AND amutak.status NOT IN ('V', 'O') AND amutas.reknr in (1161)
           GROUP BY
              amutas.reknr,amutas.faktuurnr,cicmpy.cmp_name,amutas.project) memoriaal ON faktuur.faknr = memoriaal.faknr
    WHERE 
        items.assortment in (1,3)
    GROUP BY
        faktuur.faknr,totaalbedrag_regels, bedrijfsnaam,contractnr) controle
WHERE 
   factuurbedrag <> totaalbedrag_regels
ORDER BY 
   faknr

Upvotes: 0

Views: 78

Answers (1)

Vesper
Vesper

Reputation: 18747

If your amutas.bedrag and faktuur.bdr_ev_ed_val are not int type, you are hitting the common bug of comparing floating-point numbers for equality. A workaround is using the absolute difference comparison like WHERE ABS(factuurbedrag - totaalbedrag_regels) > 0.01. Correct the constant as you see fit.

Upvotes: 1

Related Questions