Re: [sqlite] Lack of "decimal" support

I too faced a similar situation.. the following thing worked set the Column type to Double and insert rows using Double data type for java.. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Lack-of-decimal-support-tp57530p74046.html Sent from the SQLite mailing list

Re: [sqlite] Lack of "decimal" support

On 28 Mar 2011, at 5:09am, Nico Williams wrote: > On Mar 27, 2011 10:20 PM, "Darren Duncan" wrote: >> >> >> SQLite already has Blobs, and I see those as being exactly the same thing > > Blobs are _octet_ strings. Sure, one could write functions that encode and >

Re: [sqlite] Lack of "decimal" support

On Mar 27, 2011 10:20 PM, "Darren Duncan" wrote: > > Nico Williams wrote: > > User defined types. There are two types in particular that I'd like > > to see added: > > > > - Bit strings. Bit strings are like character strings, but the > > elements can only be bits.

Re: [sqlite] Lack of "decimal" support

On Sun, Mar 27, 2011 at 10:20 PM, Darren Duncan wrote: > Nico Williams wrote: >> User defined types.  There are two types in particular that I'd like >> to see added: >> >>  - Bit strings.  Bit strings are like character strings, but the >> elements can only be bits.  The

Re: [sqlite] Lack of "decimal" support

Nico Williams wrote: > User defined types. There are two types in particular that I'd like > to see added: > > - Bit strings. Bit strings are like character strings, but the > elements can only be bits. The key is that bit string length matters > when it comes to collation (000 sorts before

Re: [sqlite] Lack of "decimal" support

On Sun, Mar 27, 2011 at 11:33 AM, jeff archer <jarch...@yahoo.com> wrote: >>From: Patrick Earl <pate...@patearl.net> >>Subject: Re: [sqlite] Lack of "decimal" support >> >>If SQLite can't decide on a base-10 format itself, perhaps the answer >&g

Re: [sqlite] Lack of "decimal" support

On 27 Mar 2011, at 7:17pm, Darren Duncan wrote: > Simon Slavin wrote: >> I forgot a bunch of functions. You need to be able to do comparisons, so you >> can determine whether one decimal is greater, less or equal to another. And >> users will want abs(), max(), min(), round(), avg(), sum(),

Re: [sqlite] Lack of "decimal" support

Simon Slavin wrote: > I forgot a bunch of functions. You need to be able to do comparisons, so you > can determine whether one decimal is greater, less or equal to another. And > users will want abs(), max(), min(), round(), avg(), sum(), and total(). What is this "total" function you speak of

Re: [sqlite] Lack of "decimal" support

>From: Patrick Earl <pate...@patearl.net> >Subject: Re: [sqlite] Lack of "decimal" support > >If SQLite can't decide on a base-10 format itself, perhaps the answer >lies in enhancing the API to allow for custom type storage and >operators. >   So, like

Re: [sqlite] Lack of "decimal" support

On 27 Mar 2011, at 11:49am, Simon Slavin wrote: > decimal + decimal --> decimal > decimal - decimal --> decimal > decimal * real --> decimal > decimal / real --> decimal > decimal / decimal --> real I forgot a bunch of functions. You need to be able to do comparisons, so you can determine

Re: [sqlite] Lack of "decimal" support

>This page has a lot of info about >Decimal Number support, including >a set of libraries: > >http://speleotrove.com/decimal/ Yes! IBM and Intel are two of the big names having done significant work in this direction and made research and/or results publicly available.

Re: [sqlite] Lack of "decimal" support

>There are many cases where people are doing calculations or using >numbers expecting them to retain all digits. This would allow the BCD >type to be used for that if they really need it. Currency conversions (rarely exact!) or tax (or margin) calculations come to mind as very common uses

Re: [sqlite] Lack of "decimal" support

_ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Simon Slavin [slav...@bigfraud.org] > Sent: Sunday, March 27, 2011 5:49 AM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] Lack of "decimal" sup

Re: [sqlite] Lack of "decimal" support

s Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Sunday, March 27, 2011 5:49 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Lack of "decimal" support F

Re: [sqlite] Lack of "decimal" support

For those who are scared by this, there really isn't that much to do. Let us, for the same of argument, say we're going to allow decimal values to an arbitrary length: any number of digits, possibly with a decimal point somewhere along the string, possibly starting with a minus sign. The

Re: [sqlite] Lack of "decimal" support

On Mar 26, 2011, at 10:15 PM, BareFeetWare wrote: > On 27/03/2011, at 12:39 PM, Patrick Earl wrote: > >> Base-10 numbers are frequently used in financial calculations because >> of their exact nature. SQLite forces us to store decimal numbers as >> text to ensure precision is not lost.

Re: [sqlite] Lack of "decimal" support

On 27/03/2011, at 3:04 PM, Patrick Earl wrote: > If you use a view to return a double, you've lost the exact value you were > trying to save by storing the decimal as a text value. I'm not suggesting storing as a text value. I'm suggesting storing as an integer and only converting to a float

Re: [sqlite] Lack of "decimal" support

Patrick Earl wrote: > On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan > wrote: >> You could store your exact precision numbers as a pair of integers >> representing >> a numerator/denominator ratio and then have math operators that work on these >> pairs like they were

Re: [sqlite] Lack of "decimal" support

On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan wrote: > You could store your exact precision numbers as a pair of integers > representing > a numerator/denominator ratio and then have math operators that work on these > pairs like they were one number.  You would then

Re: [sqlite] Lack of "decimal" support

If you use a view to return a double, you've lost the exact value you were trying to save by storing the decimal as a text value. If you continue to work with it as an integer, it's exact, but that requires continual awareness of the number of decimal places at any point in time. In essence, you

Re: [sqlite] Lack of "decimal" support

Patrick Earl wrote: > That is true, but then when you are formulating generic queries within > a place such as an ORM like NHibernate, you would need to figure out > when to translate the user's "100" into "1". As well, if you > multiplied numbers, you'd need to re-scale the result. For

Re: [sqlite] Lack of "decimal" support

On 27/03/2011, at 2:09 PM, Patrick Earl wrote: > if you're in a context where you don't have significant understanding of the > user's query, how do you determine if 1.05 is $1.05 or 105%? Can you give us a bit more background and an example of this? How is the interface for the query

Re: [sqlite] Lack of "decimal" support

You're right, it doesn't make sens to multiply dollars, but if you're in a context where you don't have significant understanding of the user's query, how do you determine if 1.05 is $1.05 or 105%? I understand that one can custom-code everything for SQLite and get reasonable results in some

Re: [sqlite] Lack of "decimal" support

> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare > wrote: >> Can you store all money amounts as integers, as the cents value? That is >> exact, searchable etc. On 27/03/2011, at 1:27 PM, Patrick Earl wrote: > That is true, but then when you are formulating generic

Re: [sqlite] Lack of "decimal" support

Do money values really get multiplied together? What is the meaning of square cents as a unit? Gerry On 3/26/11, Patrick Earl wrote: > That is true, but then when you are formulating generic queries within > a place such as an ORM like NHibernate, you would need to figure

Re: [sqlite] Lack of "decimal" support

That is true, but then when you are formulating generic queries within a place such as an ORM like NHibernate, you would need to figure out when to translate the user's "100" into "1". As well, if you multiplied numbers, you'd need to re-scale the result. For example, (1 * 1) would be (100 *

Re: [sqlite] Lack of "decimal" support

On 27/03/2011, at 12:39 PM, Patrick Earl wrote: > Base-10 numbers are frequently used in financial calculations because > of their exact nature. SQLite forces us to store decimal numbers as > text to ensure precision is not lost. Unfortunately, this prevents > even simple operations such as

Re: [sqlite] Lack of "decimal" support

I've found the decimal numbers to be most generally useful in narrow ranges. For reference, here are a couple notes on how other databases implement them: MSSQL stores up to 38 digits in 17 bytes, with a specific precision. http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx

Re: [sqlite] Lack of "decimal" support

On 27 Mar 2011, at 2:39am, Patrick Earl wrote: > Base-10 numbers are frequently used in financial calculations because > of their exact nature. SQLite forces us to store decimal numbers as > text to ensure precision is not lost. Unfortunately, this prevents > even simple operations such as