Joining on Mismatched Values

Published: June 13, 2007

Hey, Ted:

Like many shops, we have a mixture of database files that we have acquired from different sources due to mergers and acquisitions over the years. Getting things to match up is challenging, to put it mildly. I have two files that contain warehouse code fields, but the codes do not match exactly. In one file, a certain warehouse is indicated by the letter T. In the other file, the same warehouse is the character 1 (one). All other warehouses have the same codes in both files.

I need to join these files, but I don't know what to do about the warehouse with different codes. I thought of setting up a cross-reference file, but wondered if there is any way to avoid it. A cross-reference file would be yet another file to maintain when we add new warehouses. Is there a way to tell SQL that T in one file matches 1 in the other file?

--CS

My gut feeling is that the warehouse cross-reference file is probably your best way to go, unless you have only one program that needs to carry out such a join. Anyway, you'll have to be the judge of that.

If you want to avoid the cross reference, use a CASE statement in your join. Here's an example that uses two files, XACT and MASTER. Warehouse T in XACT is warehouse 1 in MASTER.

SELECT xact.KEY, xact.whs, mast.WhsID, mast.WhsName 
  FROM xact AS xact 
  LEFT JOIN master AS mast 
    ON CASE 
         WHEN xact.whs = 'T' 
           THEN '1' 
           ELSE xact.whs 
        END = mast.WhsID

The CASE converts T to 1, but leaves other warehouse codes as they are. The value from the CASE is compared to the WhsID field in the MASTER file.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot