Re: [sqlite] Is there a way to do comparison of text in a numerical way.

2007-12-10 Thread Dennis Cote 

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.

2007-12-10 Thread Mag. Wilhelm Braun 

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.

2007-12-09 Thread Mag. Wilhelm Braun 

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 txt10.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.

2007-12-09 Thread John Stanton 
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 txt10.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.

2007-12-09 Thread Mag. Wilhelm Braun 

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 txt10.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]
-

[sqlite] Is there a way to do comparison of text in a numerical way.

2007-12-08 Thread Mag. Wilhelm Braun 

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 txt10.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]
-

Re: [sqlite] Is there a way to do comparison of text in a numerical way.

2007-12-08 Thread John Stanton 
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 txt10.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]
-