September 7, 2011 at 8:53 am
Hello all. I'm hoping there is a solution to this problem. I work with occupational codes. And these codes get changed and updated periodically. Unfortunately, not every agency switches over at the same time. So some table use the old codes, and some the new. On our web apps, if a user wishes to have more information on an occupation (Computer programmers, say) they click on that code which brings them to another site with a detailed description of the occupation. But these two sites are really populated by two different programs using different codes. So if the code is "old" they get a "no data available" screen. But there is data available for the "new" code. So the question is -- How do I right a statement that basically says "If code = '151011' produces no results, go to the OldAndNewCrosswalk table and find the new code and use that one."
Any help would be much appreciated.
Amy
September 7, 2011 at 8:57 am
Left join to both code lookups.
Then use coalesce in the select.
September 7, 2011 at 9:57 am
I made some test data for example of what I mean:
SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.demo') IS NOT NULL
DROP TABLE demo;
GO
CREATE TABLE dbo.demo
(
wage decimal NULL,
County varchar(20) NULL,
OldCode varchar(6) NULL,
NewCode varchar(6)NULL
);
GO
INSERT dbo.demo(wage, County, OldCode, NewCode)
VALUES
(10.00, 'Dakota', '151011', '151111'),
(10.00, 'Dakota', '113111', '113041'),
(30.00, 'Dakota', '113121', '113049'),
(30.00, 'Dakota', '113131', '113042'),
(10.00, 'Polk', '151011', '151111'),
(10.00, 'Polk', '113111', '113041'),
(12.00, 'Polk', '113121', '113049'),
(12.00, 'Polk', '113131', '113042');
GO
SET NOCOUNT OFF;
GO
SELECT *
from dbo.demo
The resulting table looks just like the results after left joining the crosswalk table:
wageCountyOldCodeNewCode
10Dakota151011151111
10Dakota113111113041
30Dakota113121113049
30Dakota113131113042
10Polk151011151111
10Polk113111113041
12Polk113121113049
12Polk113131113042
As you can see, COALESCE won't work (correct?) in this situation since both of the codes do exist, and therefore one of the columns would not be null.
I was experiementing with something like this:
DECLARE @code varchar(6) = '151051'
DECLARE @code2 varchar(6) = (select [newcode]
FROM [dbo].[CrossWalkTable]
WHERE oldcode= @code)
SELECT * FROM [dbo].[DataTable]
where occcode = @code OR occcode = @code2
Which works pretty good, if there is a one to one relationship. However old codes and new codes can also be a one to many and many to many relationship. So the code to declare @code2 doesn't work.
September 7, 2011 at 10:01 am
COALESCE returns the FIRST non-null it finds (doesn't matter if you don't feed any nulls). If you need more complex logic than this then you can use a case statement.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply