1

I created sqlite database in android as follows:

db.execSQL("CREATE TABLE ratecard (fat NUMERIC, lac NUMERIC, snf NUMERIC, 
cow NUMERIC, buf NUMERIC)");

Then I Inserted records in it as follows 

DecimalFormat degreeformat = new DecimalFormat("#.#");
DecimalFormat rateformat = new DecimalFormat("#.##");
Double csn = 0.20;
Double bsn = 0.00;
Double cft = 0.50;
Double bft = 0.00;
Double crt = 20.00 - cft - csn;
Double brt = 0.00 - bft - bsn;
for (Double ss = 7.0; ss <= 8.0); ss = ss + .1) {
    ProcSnf.setText(degreeformat.format(ss));
    crt =   crt + csn;
    brt =   brt + bsn;
    Double crrt =   crt;
    Double brrt =   brt;
    for (Double ff = Double.valueOf(fromfat); ff <= Double.valueOf(tofat); 
    ff = ff + .1) {
        ProcFat.setText(degreeformat.format(ff));
        Double l    =   (ss - 0.36 - (ff * .21)) * 4.0;
        ll = Double.valueOf(degreeformat.format(l));
        crrt = crrt + cft;
        brrt = brrt + bft;
        DB.DeleteSelectedRateCard(ff, ss);
        DB.AddRateCard(ll, ff, ss, crrt, brrt);
     }
}

Records created in database. Then I pulled this database from emulator to hard disk. Then I browse this data using sqlite browser. All records shown properly. Now filter records in browser for fat = 5.0. All records shown. Now going to Execute SQL tab in Sqlite Browser I tried following sql statement and getting following result.

  1. select * from ratecard where fat = 3.5 (12 rows returned)
  2. select * from ratecard where fat = 4.1 (12 rows returned)
  3. select * from ratecard where snf = 7.0 (18 rows returned including fat > 4.1)
  4. select * from ratecard where fat = 4.2 (0 rows returned "this result is upto fat = 5.0")
  5. select * from ratecard where fat > 4.1 (108 rows returned)
    This problem also occurs when I fire query in android app. So my problem is Is there problem in creating table or problem is in query statement.
Nic3500
  • 8,144
  • 10
  • 29
  • 40
Makarand
  • 11
  • 2
  • What results were you expecting? Give some sample data that gets inserted into the table. And show your AddRateCard method while you're at it. – Shawn Aug 11 '18 at 02:46
  • Thanks Shawn. Records are inserted in ratecard table properly. If I browse these records in SQLite browser, I see all records properly. Sql commands relating to columns lactos or snf fire properly. When I fire sql command for column fat up to value 4.1 it works fine. But when I fire sql command for fat = 4.2 it shows 0 records. But if I fire sql command for fat > 4.1 and fat < 4.3 it shows all the records relating to fat = 4.2. I don't know how to attach file to my question otherwise I will attach my database file.It size only 56K – Makarand Aug 13 '18 at 02:29
  • Okay, yeah. You can't use straight equality to compare floating point numbers. [Some reading](https://randomascii.wordpress.com/2012/02/25/comparing-floating-point-numbers-2012-edition/). – Shawn Aug 13 '18 at 05:47
  • Hi, actually all the fields in ratecard table are floating. But this problem is only for fat field. If I fire query for lacto or snf field they work properly. – Makarand Aug 13 '18 at 05:56

0 Answers0