SQL Query hangs forever when joining on decimal(8,0) fields

Question

Hi there. I have the following query, which works fine:

SELECT      TMP_CLPUD.dbo.FormatS(RIGHT(SV1.VASLOC, 7), 9, 0) AS C_ACCOUNT, 
        TMP_CLPUD.dbo.FormatS(Cust.C_CUSTOMER, 7, 0) AS C_CUSTOMER, 
        CA.OID, 
        CN2.NBCUNQ AS CUNQ, 
        CN2.NBSUNQ AS SUNQ, 
        'AC' AS C_STATUS,
        TMP_CLPUD.dbo.mmsDATE(CN2.NBCNTE) AS IN_DATE, 
        TMP_CLPUD.dbo.mmsDATE(CN2.NBDSTE) AS OUT_DATE, 
        0 AS ARCHIVE
FROM        BILLING.SVP00100 AS SV1 INNER JOIN BILLING.CNP00200 AS CN2 ON SV1.VASUNQ = CN2.NBSUNQ
                                INNER JOIN BILLING.CMP00100 AS CM1 ON CN2.NBCUNQ = CM1.KACUST
                                INNER JOIN BILLING.CustomerAccounts AS CA ON CM1.KACUST = CA.CustomerNbr
                                INNER JOIN TMP_CLPUD.dbo.tblCustomers As Cust ON CA.OID = Cust.OID
                                INNER JOIN BILLING.Customers AS C ON CA.OID = C.OID
                                INNER JOIN BILLING.CNP00100 AS CN1 ON CN2.NBCUNQ = CN1.NACUNQ AND CN2.NBSUNQ = CN1.NASUNQ
ORDER BY C_ACCOUNT, C_CUSTOMER

However, if I try to link one more field between CN1 and CN2, the query will hang indefinitely. The field is type decimal(8,0) in both tables and does not allow for nulls. Here is the query with the third link in place:

SELECT      TMP_CLPUD.dbo.FormatS(RIGHT(SV1.VASLOC, 7), 9, 0) AS C_ACCOUNT, 
        TMP_CLPUD.dbo.FormatS(Cust.C_CUSTOMER, 7, 0) AS C_CUSTOMER, 
        CA.OID, 
        CN2.NBCUNQ AS CUNQ, 
        CN2.NBSUNQ AS SUNQ, 
        'AC' AS C_STATUS,
        TMP_CLPUD.dbo.mmsDATE(CN2.NBCNTE) AS IN_DATE, 
        TMP_CLPUD.dbo.mmsDATE(CN2.NBDSTE) AS OUT_DATE, 
        0 AS ARCHIVE
FROM        BILLING.SVP00100 AS SV1 INNER JOIN BILLING.CNP00200 AS CN2 ON SV1.VASUNQ = CN2.NBSUNQ
                                INNER JOIN BILLING.CMP00100 AS CM1 ON CN2.NBCUNQ = CM1.KACUST
                                INNER JOIN BILLING.CustomerAccounts AS CA ON CM1.KACUST = CA.CustomerNbr
                                INNER JOIN TMP_CLPUD.dbo.tblCustomers As Cust ON CA.OID = Cust.OID
                                INNER JOIN BILLING.Customers AS C ON CA.OID = C.OID
                                INNER JOIN BILLING.CNP00100 AS CN1 ON CN2.NBCUNQ = CN1.NACUNQ AND CN2.NBSUNQ = CN1.NASUNQ AND CN2.NBCNTE = CN1.NACNTE
ORDER BY C_ACCOUNT, C_CUSTOMER

I've rewritten this thing as many different ways as I could think but every time I try to link that third field it just hangs forever. Any suggestions?

Answer 1

If you're going to compare floating point numbers for an exact match, you're usually asking for trouble. One way to overcome this is to take the absolute value of the difference of the 2 numbers and compare it to an epsilon, say 1e-9, and if the difference is less than or equal to this epsilon, then you can assume the 2 floating point numbers are equal.

EDIT As Lasse points out, it will be a mess to try and join these columns using the technique described above. If you really need this join capability, another approach would be to not store the numbers as floating point, but store them as integers. So if it were a dollar currency, for example, you could store $123.45 as 12345. $99.01 would be stored as 9901. Then you could join the integer values, which would avoid all the headaches with floating point numbers. To convert the integer numbers back to their corresponding floating point value, you just divide by some constant (100 in the dollar currency example).

But I would question why you are joining these types of values, it seems kind of weird.

Answer 2

Equality is always a little sketchy with floating point numbers. If you're representing something like monetary transactions, you're usually better off storing them in an integer type, like cents (or tenths of cents, or whatever the smallest coin you're willing to deal with is).