Question about size of numbers in SQL 'ON'

[VickyC]

hello
I have a question about the 'ON' statement in my query. I'll only show the necessary part of the SQL:

SELECT T1.* 
FROM        tbl1 as T1
INNER JOIN  tbl2 As T2
ON  (T1.a*10000000000 +  T1.b*1000000000 + T1.c*100000000 + ..... + T1.i*10 + T1.j) >= 
    (T2.a*10000000000 +  T2.b*1000000000 + T2.c*100000000 + ..... + T2.i*10 + T2.j)
......

In both tables, the values a, b, c...j have been declared as INTEGERS. Each of them has a value from 0 to 6.

Here's my question. Is it possible that the 'ON' statement could give erroneous results, given that either side of the inequality could be so large (even greater than the max value of LONG). As far as I know, the query seems to work perfectly, but I worry that the size of the terms in the ON statement could be giving some wrong or incomplete results.

thank you
Vicky C.

 

[lameid]

My hunch is that access will do implicit conversions to a datatype that is large enough assuming it has one.

That said, this is inherently slow. And I am not certain without testing.

I suspect your comparison can be simplified but wasn't sure since the ommitted columns have different predecessors and succcessors as far as formulas are concerned.

 

[Andrzejek]

I hate to state the obvious, but in the example you gave it looks to me you want to check if SUM of certain fields in one table is grater or equal to the SUM of corresponding fields in another table.

If that’s the case, why even bother with multiplications?

Have fun.

---- Andy

 

[lameid]

Andy,

Have a look at the OP's last two terms.... I was thinking the same at first, but I still think something can be done to simplify the expressions (maybe not logically but to work arithmetically within datatype constraints).

 

[PHV]

Each of them has a value from 0 to 6
So what about this ?

ON  (T1.a & T1.b & T1.c & ..... & T1.i & T1.j) >= 
    (T2.a & T2.b & T2.c & ..... & T2.i & T2.j)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886