August 19, 2010 at 2:05 pm
I've got a database where records are broken down into various categories using a series of columns. In the SQL code below, the 'cat' and 'subcat' columns are used to come up with a corresponding 'code' value (in my real life app, five or more different columns will be used to derive a 'code' value).
Essentially, I need to convert/combine the mess of multiple cat/subcat/etc columns into new two-digit codes and there are thousands of unique cat/subcat/etc combinations.
My first thought was to create a conversion table (see a truncated version below) and use a LEFT JOIN ON cat/subcat columns to get the desired code value. This almost works, but not quite.
It does work when the values for cat/subcat are in my conversion table, but if the subcat value is not found in the conversion table, then I get a NULL value for code.
Here's what I'd like:
- If both cat and subcat have a match in the conversion table, use the corresponding code.
- If cat does not have a match, then code = NULL.
- If cat matches, but subcat does not match, then code = (a default value).
In my conversion table, I created a subcat of '_DEFAULT_' to indicate this.
It makes sense to do it this way because there may be 100 or more rows where cat = 2 and only a few of them will have a different code. Also, there is no set list of valid subcat codes (it can change), so I'm hoping this approach will allow occasional additions or corrections to be made to the master conversion table. Note: this is not a one-time batch conversion process, but will be adapted for some old queries and used in new ones.
Query #1 doesn't account for NULL subcat codes, so I came up with Query #2, which is better than the first one, but still doesn't do account for the _DEFAULT_ requirement. Query #3 actually does what I want, but there's got to be a better, more efficient way to do it, right? This becomes a bigger issue when I adapt this sample code into my real world application (where there are more columns). I'm thinking there's got to be a way to do this entirely within the JOIN clause.
I welcome your feedback and suggestions. Thank you.
DECLARE @conversion TABLE (
cat INT,
subcat VARCHAR(16),
code CHAR(2)
)
INSERT INTO @conversion (cat, subcat, code) VALUES (2, 'A30', 'AA')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, 'A26', 'BB')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, 'B27', 'CC')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, 'X28', 'CC')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, 'L29', 'DD')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, 'CC1', 'AB')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, '911', 'CZ')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, '_DEFAULT_', 'XX')
INSERT INTO @conversion (cat, subcat, code) VALUES (2, NULL, 'XX')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, 'G03', 'MD')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, 'K55', 'DA')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, 'B52', 'LB')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, 'H20', 'OZ')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, 'Y0U', 'PT')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, 'ABC', 'PT')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, '227', 'PT')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, '_DEFAULT_', 'XY')
INSERT INTO @conversion (cat, subcat, code) VALUES (52, NULL, 'XY')
DECLARE @test-2 TABLE (
cat INT,
subcat VARCHAR(16)
)
INSERT INTO @test-2 (cat, subcat) VALUES (2, 'A30') -- code = AA
INSERT INTO @test-2 (cat, subcat) VALUES (2, 'X28') -- code = CC
INSERT INTO @test-2 (cat, subcat) VALUES (2, 'B27') -- code = CC
INSERT INTO @test-2 (cat, subcat) VALUES (2, NULL) -- code = XX
INSERT INTO @test-2 (cat, subcat) VALUES (2, '_OTHER_') -- code = XX
INSERT INTO @test-2 (cat, subcat) VALUES (52, 'K55') -- code = DA
INSERT INTO @test-2 (cat, subcat) VALUES (52, 'ABC') -- code = PT
INSERT INTO @test-2 (cat, subcat) VALUES (52, NULL) -- code = XY
INSERT INTO @test-2 (cat, subcat) VALUES (99, '_ANOTHER_') -- code = NULL
-- Query 1
SELECT
T.cat,
T.subcat,
X.code
FROM @test-2 AS T
LEFT JOIN @conversion AS X
ON T.cat = X.cat
AND T.subcat = X.subcat
-- Query 2
SELECT
T.cat,
T.subcat,
X.code
FROM @test-2 AS T
LEFT JOIN @conversion AS X
ON T.cat = X.cat
AND ISNULL(T.subcat, '_DEFAULT_') = X.subcat
-- Query 3
SELECT
T.cat,
T.subcat,
X.code,
COALESCE(X.code, (SELECT code FROM @conversion AS X1 WHERE X1.cat = T.cat AND X1.subcat = '_DEFAULT_')) AS code_1
FROM @test-2 AS T
LEFT JOIN @conversion AS X
ON T.cat = X.cat
AND T.subcat = X.subcat
August 19, 2010 at 3:00 pm
See if this works for you:
SELECT
T.cat,
T.subcat,
isnull(X.code, case when X.cat is NULL then NULL else DEFAULT end) as code
FROM @test-2 AS T
LEFT JOIN @conversion AS X
ON T.cat = X.cat
AND T.subcat = X.subcat
You can nest case statements as needed to handle additional 'subcat' columns.
The probability of survival is inversely proportional to the angle of arrival.
August 20, 2010 at 6:48 am
Thanks for the suggestion, but the results appear to be the same as my first example.
What I'd really like to do is get it all contained in the JOIN clause. The COALESCE in the third query works (it also works as ISNULL), but it is so ... inelegant. It really seems like I should be able to do it in the JOIN, but I can't figure out how to do it.
August 20, 2010 at 7:00 am
Actually, I got something that works ...
SELECT
T.cat,
T.subcat,
X.code
FROM @test-2 AS T
LEFT JOIN @conversion AS X
ON X.cat = T.cat
AND X.subcat = CASE
WHEN EXISTS (
SELECT subcat FROM @conversion AS X1
WHERE X1.cat = T.cat AND X1.subcat = T.subcat
) THEN T.subcat
ELSE '_DEFAULT_'
END
Is this the best way to do it? When I finally understand the concept, I'll be deploying this in the real world where there are another four columns with subcategories, so it looks like it could ugly pretty quickly.
Thanks again.
August 20, 2010 at 8:53 am
I would tend to think so. The only other way I can think of off hand is to do two JOINS to the table to check for each case like:
SELECT
T.cat,
T.subcat,
isnull(X.code, case when Y.cat is NULL then NULL else DEFAULT end) as code
FROM @test-2 AS T
LEFT JOIN @conversion AS X
ON T.cat = X.cat AND T.subcat = X.subcat
LEFT JOIN @conversion AS Y
ON T.cat = Y.cat
The probability of survival is inversely proportional to the angle of arrival.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply