SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Odd SQLite query problem  (Read 1431 times)

Scott Slater

  • Newbie
  • *
  • Posts: 7
    • View Profile
Odd SQLite query problem
« on: December 05, 2013, 03:11:44 PM »

I had a customer contact me that a report was randomly "missing data" and have tracked down the problem to a single SQL query.  the problem is that some items that logically match the WHERE clause don't get selected.  I have put together a reverse logic of that statement below by changing the = to != .  I have attached a very small subset of this clients data that shows the problem.  Using the attached file which contains 4 orders none of which should get selected using the statement below;

SELECT * FROM orders_tbl WHERE (ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)!=subtotal;

However, record #1 gets selected.  If you change the != back to an =, then you get the remaining 3.

I have updated to the latest SQLite dll file, and still get this problem with this data set.  I have manually looked at all of the data and did the math and they should all be treated the same way.   Can anyone see a reason or something that I am missing that is causing this issue?

Thanks in advance.

-Scott Slater
Logged

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Odd SQLite query problem
« Reply #1 on: December 09, 2013, 08:17:29 AM »

It is a rounding issue using floating point numbers.

Solution 1:
select * from orders_tbl where round(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee,2)=subtotal;

Solution 2:
Save a penny as 1 and a dollar as 100 is the way I have always handled money.
All INTEGERs and display results multiplied by .01 avoids floating point errors and no rounding is needed.


drop table if exists orders_tbl;
create table if not exists orders_tbl(ord_total,discount,tax1,tax2,tax3,tax4,delivery_tax,delivery_fee,subtotal);
insert into orders_tbl values(38.55, 0, 2.42, 0, 0, 0, .1, 1.5, 34.53);
insert into orders_tbl values(3855, 0, 242, 0, 0, 0, 10, 150, 3453);
------------------------------------------------------------------------
select *' from orders_tbl where (ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)!=subtotal;

38.55  0  2.42  0  0  0  0.1  1.5  34.53  rounding error
------------------------------------------------------------------------
select * from orders_tbl where round(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee,2)=subtotal;

38.55  0  2.42  0  0  0  0.1  1.5  34.53  used round 
3855  0  242  0  0  0  10  150  3453     used round 
------------------------------------------------------------------------
select * from orders_tbl where (ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee)=subtotal;
3855  0  242  0  0  0  10  150  3453      multiply results by .01 

PB test program using ROUND.
Code: [Select]
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL RowNum,ColNum, columns AS LONG, sResults AS STRING
  slopen "Test.db3","C"
  slexe "CREATE TABLE IF NOT EXISTS ORDERS_TBL(ord_total,discount,tax1,tax2,tax3,tax4,delivery_tax,delivery_fee,subtotal)"
  slexe "INSERT INTO ORDERS_TBL VALUES(38.55,0,2.42,0,0,0,.1,1.5,34.53)"
  slsel "SELECT * FROM orders_tbl WHERE " + _
    "ROUND(ord_total+discount-tax1-tax2-tax3-tax4-delivery_tax-delivery_fee,2)=subtotal"
  columns = slGetColumnCount
  DO WHILE slGetRow
    FOR ColNum = 1 TO columns
      sResults = sResults + slf(ColNum) + "   "
    NEXT
    sResults = sResults + $CRLF
  LOOP
  ? sResults,,"Results"
END FUNCTION


« Last Edit: December 17, 2013, 06:59:18 AM by cj »
Logged
CJ

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Avoid rounding displaying integers as money
« Reply #2 on: August 15, 2018, 02:20:21 AM »

To display integers as money
select rowid,printf('%.2f',price*.01) as price,product from parts
Logged
CJ