On 2006-10-04 07:05, George H wrote:
Hello Again:
I'm trying to do some clinical research on large data sets and I ran into a problem represented in the following simplified example:
geo-> select * from test2; +-----------+-----------+ | a | b | +===========+===========+ | 3.05 | 3.07 | | 3.06 | 3.04 | | 3.13 | 3.16 | | 3.22 | 3.22 | | 3.05 | 3.05 | | 3.07 | 3.06 | | 3.08 | 3.05 | | 3.12 | 3.09 | | 3.20 | 3.17 | | 3.17 | 3.20 | | 3.09 | 3.11 | | 2.74 | 2.73 | | 2.79 | 2.78 | | 2.80 | 2.81 | +-----------+-----------+ 14 rows geo-> select * from test2 where a=b; +-----------+-----------+ | a | b | +===========+===========+ | 3.22 | 3.22 | | 3.05 | 3.05 | +-----------+-----------+ 2 rows geo-> select * from test2 where (a/b)-1=0; +-----------+-----------+ | a | b | +===========+===========+ | 3.06 | 3.04 | | 3.22 | 3.22 | | 3.05 | 3.05 | | 3.07 | 3.06 | | 3.08 | 3.05 | | 3.12 | 3.09 | | 3.20 | 3.17 | | 2.74 | 2.73 | | 2.79 | 2.78 | +-----------+-----------+ <>9 rows geo-> select * from test2 where (b/a)-1=0; +-----------+-----------+ | a | b | +===========+===========+ | 3.05 | 3.07 | | 3.13 | 3.16 | | 3.22 | 3.22 | | 3.05 | 3.05 | | 3.17 | 3.20 | | 3.09 | 3.11 | | 2.80 | 2.81 | +-----------+-----------+ <>7 rows I thought that if a=b, then (a/b)-1 and (b/a)-1 must both also be equal to 0.
Columns a and b are type decimal(7,2) and the data was entered to two decimal places by hand using insert statements.
Thanks and best.......George
With your data, consider this: sql>select a,b,a/b,b/a from test2; # sys.test2, sys.test2, sys., sys. # table_name # a, b, sql_div_a, sql_div_b # name # decimal, decimal, decimal, decimal # type # 9, 9, 11, 11 # length [ 3.05, 3.07, 0.99, 1.00 ] [ 3.06, 3.04, 1.00, 0.99 ] [ 3.13, 3.16, 0.99, 1.00 ] [ 3.22, 3.22, 1.00, 1.00 ] [ 3.05, 3.05, 1.00, 1.00 ] [ 3.07, 3.06, 1.00, 0.99 ] [ 3.08, 3.05, 1.00, 0.99 ] [ 3.12, 3.09, 1.00, 0.99 ] [ 3.20, 3.17, 1.00, 0.99 ] [ 3.17, 3.20, 0.99, 1.00 ] [ 3.09, 3.11, 0.99, 1.00 ] [ 2.74, 2.73, 1.00, 0.99 ] [ 2.79, 2.78, 1.00, 0.99 ] [ 2.80, 2.81, 0.99, 1.00 ] In other words, the problem (if any) lies in rounding to two decimal places. -- Sjoerd Mullender