OK...this way then...it's stored correctly and the view should display correctly in a 3rd party app. Correctly gives the 99 cent discount. Should work for any 2 decimal place number.
create table mine(a number(10,2)); create view v1 as select a/100.0 as a from mine; create trigger t1 after insert on mine for each row begin update mine set a = cast(round(new.a*100) as integer) where rowid=new.rowid; end; create trigger t2 after update on mine for each row begin update mine set a=cast(round(a*100) as integer) where typeof(a)!='integer' and rowid=new.rowid; end; insert into mine values(1.0/3.0); select * from mine; select a*3 from v1; .99 update mine set a=1/3.0; select a*3 from v1; .99 -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Monday, May 06, 2013 8:03 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to select a precision? On Mon, 6 May 2013 07:42:43 -0500 "Michael Black" <mdblac...@yahoo.com> wrote: > Yes...other databases do respect NUMBER(10,2) on SELECT's. ... > Can you simply use round to do what you want? > CREATE TABLE stuff(f number(10,2)); > INSERT INTO "stuff" VALUES(1.0/3.0); > sqlite> select f from stuff; > 0.333333333333333 > sqlite> select round(f,2) from stuff; > 0.33 To be clear, Paul van Helden isn't talking about SELECT. He's talking about INSERT, about not storing more precision that the input actually represents. Apply a 33% discount to a $3.00 purchase. Is the bill $2.00, or $2.01? If you say $2.00, then apply a 33% discount to three $1 purchases in three separate months. I imagine you'd agree the total discount is just 99 cents. Whether or not SQLite ought to support exact decimal types is a separate question; I don't think anyone is saying it should. But it isn't just a matter of presentation. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users