Joining on Mismatched Values
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
|
