June 23, 2010 at 3:29 am
Hi,
I have a staging table that contains data obtained from a xml file import via SSIS.
In instances where 'Unit' values are NULL, I need to convert these into correct values. These are based on the A_Codes. Records that contain NULL values (in Unit column) occur are where there has been a duplication of previous A_Codes (i.e. where Line_Id > 1) so there will always be a comparison to match on. Hope that makes sense!
Therefore, in the examples below, lines (PK_Id) 10, 11 & 12 need the 'Unit' values be updated.
PK_Id is the actual file 'Identity Specification' (incremented by 1)
rows where Line_Id = 1 will ALWAYS have a 'Unit' value (not NULL)
Example:
Table A (after xml import)
AB_Id, A_Code, Unit, Line_Id, PK_Id
FC002, 0041, %, 1, 1
FC002, 0043, %, 1, 2
FC002, 0044, %, 1, 3
FC002, 0045, %, 1, 4
FC002, 0046, %, 1, 5
FC002, 0047, %, 1, 6
FC002, 0049, mg/kg, 1, 7
FC002, 0052, mg/kg, 1, 8
FC002, 0055, mg/kg, 1, 9
FC002, 0044, NULL, 2, 10
FC002, 0052, NULL, 2, 11
FC002, 0052, NULL, 3, 12
Table_A (after conversions)
AB_Id, A_Code, Unit, Line_Id, PK_Id
FC002, 0041, %, 1, 1
FC002, 0043, %, 1, 2
FC002, 0044, %, 1, 3
FC002, 0045, %, 1, 4
FC002, 0046, %, 1, 5
FC002, 0047, %, 1, 6
FC002, 0049, mg/kg, 1, 7
FC002, 0052, mg/kg, 1, 8
FC002, 0055, mg/kg, 1, 9
FC002, 0044, %, 2, 10
FC002, 0052, mg/kg, 2, 11
FC002, 0052, mg/kg, 3, 12
Notes:
In the above example the 10th record (Line_Id = 10) has had the 'Unit' value changed from NULL to % (matched on row 3).
In the above example the 11th record (Line_Id = 11) has had the 'Unit' value changed from NULL to mg/kg (matched on row 8).
In the above example the 12th record (Line_Id = 12) has had the 'Unit' value changed from NULL to mg/kg (matched on row 8).
Any ideas please?
Thanks in advance,
June 23, 2010 at 3:49 am
Try the following code,
DECLARE @Table_A TABLE (AB_Id nvarchar(5), A_Code nvarchar(5), Unit nvarchar(5), Line_Id int, PK_Id int)
INSERT @Table_A
SELECT 'FC002', '0041', '%', 1, 1
UNION SELECT 'FC002', '0043', '%', 1, 2
UNION SELECT 'FC002', '0044', '%', 1, 3
UNION SELECT 'FC002', '0045', '%', 1, 4
UNION SELECT 'FC002', '0046', '%', 1, 5
UNION SELECT 'FC002', '0047', '%', 1, 6
UNION SELECT 'FC002', '0049', 'mg/kg', 1, 7
UNION SELECT 'FC002', '0052', 'mg/kg', 1, 8
UNION SELECT 'FC002', '0055', 'mg/kg', 1, 9
UNION SELECT 'FC002', '0044', NULL, 2, 10
UNION SELECT 'FC002', '0052', NULL, 2, 11
UNION SELECT 'FC002', '0052', NULL, 3, 12;
WITH Conversion (Unit, A_Code) AS (
SELECT DISTINCT
Unit, A_Code
FROM @Table_A
WHERE Line_Id = 1
)
SELECT A.AB_Id,
A.A_Code,
COALESCE(A.Unit, C.Unit) [Unit],
Line_Id,
PK_Id
FROM @Table_A A LEFT OUTER JOIN
Conversion C ON A.A_Code = C.A_Code
June 23, 2010 at 3:50 am
or:
-- *** Test Data ***
DECLARE @a TABLE
(
AB_Id char(5) NOT NULL
,A_Code char(4) NOT NULL
,Unit varchar(10) NULL
,Line_Id int NOT NULL
,PK_Id int NOT NULL
)
INSERT INTO @a
SELECT 'FC002', '0041', '%', 1, 1
UNION ALL SELECT 'FC002', '0043', '%', 1, 2
UNION ALL SELECT 'FC002', '0044', '%', 1, 3
UNION ALL SELECT 'FC002', '0045', '%', 1, 4
UNION ALL SELECT 'FC002', '0046', '%', 1, 5
UNION ALL SELECT 'FC002', '0047', '%', 1, 6
UNION ALL SELECT 'FC002', '0049', 'mg/kg', 1, 7
UNION ALL SELECT 'FC002', '0052', 'mg/kg', 1, 8
UNION ALL SELECT 'FC002', '0055', 'mg/kg', 1, 9
UNION ALL SELECT 'FC002', '0044', NULL, 2, 10
UNION ALL SELECT 'FC002', '0052', NULL, 2, 11
UNION ALL SELECT 'FC002', '0052', NULL, 3, 12
-- *** End Test Data ***
UPDATE A1
SET Unit = A2.Unit
FROM @a A1
JOIN @a A2
ON A1.A_Code = A2.A_Code
AND A2.Line_Id = 1
WHERE A1.Unit IS NULL
SELECT *
FROM @a
June 23, 2010 at 3:55 am
This was removed by the editor as SPAM
June 23, 2010 at 6:56 am
Excellent,
Thanks to all!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply