• Welcome to SQLitening Support Forum. Please login.
 
December 03, 2021, 07:44:59 PM

News:

Welcome to the SQLitening support forums!


numeric query issue

Started by D. Wilson, July 17, 2013, 01:15:01 AM

Previous topic - Next topic

D. Wilson

I created a database and have a set declared as numeric. I used an external program and the database structure says the field is numeric. I imported a bunch of data in the table. But I am having trouble retrieving data from the table.

I know there are 0 values (THE NUMBER ZERO)  in the table.
However
When I query                            SELECT * from Table WHERE fieldname =0        it returns nothing
When I query                            SELECT * from Table WHERE fieldname = '0'      it return the expected results ????
When I query                            SELECT * from Table WHERE fieldname > 0        it returns nothing
When I query                            SELECT * from Table WHERE fieldname =1.15    if returns the expected results.

All my other querys work great. It seem to be related to 0 (zero) queries.

I have used the is null syntax and sqlite does not return any value (as expected) I know the fields have a value.

I have spent over 4 hours trying to figure this out. I think I am having a 'senior' moment (But I am not that old). What am I missing. Any help/insights would be appreciated.

Jean-Pierre LEROY

Did you try with the field declared REAL instead of numeric ?

From SQLIte site :

Quote
A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)

More info here http://www.sqlite.org/datatype3.html

Bern Ertl

Not sure if you are aware, but SQLite's field/column type definitions are "affinities".  They are not strictly enforced.  If you have a typo/mistake in your code and assign a value as a string (enclosed in quotes), it will store a string value into the database.

QuoteWhen I query                            SELECT * from Table WHERE fieldname = '0'      it return the expected results

I'd look for a typo in the code that stored the data.

D. Wilson

Digging into this now. I agree that sqlite does not have hard assignments regarding field data type.
When I used then slsgetfielddatatype function. It return the all the numeric fields are float and the blank fields are text. I use the slsbuildinsertorupdate function to build the sql query. I looked at then sql syntax and it looks good.
I use the the slsFN function to return the value. I am going to go back to my original conversion. and if the value is 0 I am going to put zero instead of a blank field.



Rolf Brandt

July 19, 2013, 04:55:03 AM #4 Last Edit: July 19, 2013, 05:07:50 AM by Rolf Brandt
Fields with Null-values can be a little tricky sometimes. This works:


SELECT * FROM Table WHERE fieldname  is null


Alternatively - if you want to test for null-values and the number 0 (Zero) you can use this:


SELECT * FROM Table WHERE fieldname  is null OR fieldname = 0


I had been out for a Jazz festival in the Pfalz area of Germany for a couple of days. No computer along (except a smartphone), just enjoying the sun, good music, excellent food, and great wines. Had a great time.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

cj


drop table if exists t1;
create table if not exists t1(fieldname);
insert into t1 values(1.15);
insert into t1 values(0);
insert into t1 values(1.15);
insert into t1 values(0);
select rowid,* from t1 where fieldname = 0; 
select rowid,* from t1 where fieldname = '0';
select rowid,* from t1 where fieldname  > 0; 
select rowid,* from t1 where fieldname = 1.15;

-- 2   0
-- 4   0

-- nothing

-- 1   1.15
-- 3   1.15

-- 1   1.15
-- 3   1.15



drop table if exists t1;
create table if not exists t1(fieldname NUMERIC);
insert into t1 values(1.15);
insert into t1 values(0);
insert into t1 values(1.15);
insert into t1 values(0);
select rowid,* from t1 where fieldname = 0; 
select rowid,* from t1 where fieldname = '0';
select rowid,* from t1 where fieldname  > 0; 
select rowid,* from t1 where fieldname = 1.15;

-- 2   0
-- 4   0

-- 2   0
-- 4   0

-- 1   1.15
-- 3   1.15

-- 1   1.15
-- 3   1.15

D. Wilson

CJ can you elaborate on what you are finding. Should I use float or not assign any data type for the field

I have an sqlite reference book and it stated that the field data types are ignored. And data put in the database is stored and retreived.

What I found it is if i enter a zero in the field. It retreives the records properly. So I wrote a routine and if a numeric field is blank I insert a zero. In a way this is a good thing. I never have to be concerned about nulls. And my queries seem to work properly.

cj

July 20, 2013, 08:40:44 PM #7 Last Edit: July 21, 2013, 09:12:12 AM by cj
Correct.
Put a 0 in the empty numeric fields and forget about nulls, blanks and quoted numeric fields.
create table if not exists t1(fieldname);
insert into t1 values(0);


Use this: create table t1(fieldname)
Do not use NUMERIC: create table t1(fieldname NUMERIC)