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: [email protected]
[mailto:[email protected]] On Behalf Of James K. Lowden
Sent: Monday, May 06, 2013 8:03 PM
To: [email protected]
Subject: Re: [sqlite] Is there a way to select a precision?
On Mon, 6 May 2013 07:42:43 -0500
"Michael Black" <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
