Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Thanks for the hint. W.Braun Dennis Cote wrote: Mag. Wilhelm Braun wrote: I thought that this might properly a bigger thing. Well, I found a solution which fits my purpose at the moment. ( SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) ) I do not use selection of max() or min() very often - it seems it is the best suiting solution (effort - result) at the moment. I think you should probably use a query like the following: select txt from test where cast(txt as real) = (select max(cast(txt as real)) from test) Which applies the same cast to each row for the comparison that it applied to each row for the max value determination. This cast may be done implicitly by SQLite, but it is probably safer to make it explicit. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Mag. Wilhelm Braun wrote: I thought that this might properly a bigger thing. Well, I found a solution which fits my purpose at the moment. ( SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) ) I do not use selection of max() or min() very often - it seems it is the best suiting solution (effort - result) at the moment. I think you should probably use a query like the following: select txt from test where cast(txt as real) = (select max(cast(txt as real)) from test) Which applies the same cast to each row for the comparison that it applied to each row for the max value determination. This cast may be done implicitly by SQLite, but it is probably safer to make it explicit. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Thanks, I thought that this might properly a bigger thing. Well, I found a solution which fits my purpose at the moment. ( SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) ) I do not use selection of max() or min() very often - it seems it is the best suiting solution (effort - result) at the moment. regards W.Braun John Stanton wrote: We built a fixed point arithmetic library using text strings. The format stored is right justified, leading space filled decimal numbers with embedded decimal points and leading sign. The purpose of that is not for arithmetic efficiency but so that they can be directly output into a printed page or HTML document. The algorithms we use are essentially from Knuth's Semi Numerical Algorithms volume. Functions exist for the common arithmetic operations plus moves and comparisons. Rounding is implemented using the algorithm which minimizes skew. These functions are also added into Sqlite as custom functions so that the decimal numbers can be used from SQL. We define the decimal numbers using standard SQL with precision and scale assigned in the type declaration. Sqlite's ability to store declared types makes the integration possible. This is not a simple fix, but it does let us produce accurate financial reports. Mag. Wilhelm Braun wrote: Thanks Stanton, could you elaborate a bit on that - I'm not sure if I get exactly what you mean. if you have a smallish example would be great. the help from 'Igor Tandetnik' with cast is a good starting point - but on selections with max or min I still get the incorrect rounded numbers back. EXAMPLE:column txt "0.2009" "10.200899" "4.0" "300.2009" and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test it returns returns 300.2008 so the only solution till now seems to make a sub-query like: SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) not sure how messy that might get in complex queries. anyway for any suggestion I'm more than grateful regards W.Braun John Stanton wrote: Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection.example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" "3.2009" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
We built a fixed point arithmetic library using text strings. The format stored is right justified, leading space filled decimal numbers with embedded decimal points and leading sign. The purpose of that is not for arithmetic efficiency but so that they can be directly output into a printed page or HTML document. The algorithms we use are essentially from Knuth's Semi Numerical Algorithms volume. Functions exist for the common arithmetic operations plus moves and comparisons. Rounding is implemented using the algorithm which minimizes skew. These functions are also added into Sqlite as custom functions so that the decimal numbers can be used from SQL. We define the decimal numbers using standard SQL with precision and scale assigned in the type declaration. Sqlite's ability to store declared types makes the integration possible. This is not a simple fix, but it does let us produce accurate financial reports. Mag. Wilhelm Braun wrote: Thanks Stanton, could you elaborate a bit on that - I'm not sure if I get exactly what you mean. if you have a smallish example would be great. the help from 'Igor Tandetnik' with cast is a good starting point - but on selections with max or min I still get the incorrect rounded numbers back. EXAMPLE:column txt "0.2009" "10.200899" "4.0" "300.2009" and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test it returns returns 300.2008 so the only solution till now seems to make a sub-query like: SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) not sure how messy that might get in complex queries. anyway for any suggestion I'm more than grateful regards W.Braun John Stanton wrote: Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection.example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" "3.2009" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Thanks Stanton, could you elaborate a bit on that - I'm not sure if I get exactly what you mean. if you have a smallish example would be great. the help from 'Igor Tandetnik' with cast is a good starting point - but on selections with max or min I still get the incorrect rounded numbers back. EXAMPLE:column txt "0.2009" "10.200899" "4.0" "300.2009" and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test it returns returns 300.2008 so the only solution till now seems to make a sub-query like: SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) not sure how messy that might get in complex queries. anyway for any suggestion I'm more than grateful regards W.Braun John Stanton wrote: Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection.example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" "3.2009" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection. example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -