March 11, 2009 at 10:34 am
I'm embarrassed that I can't remember how to get this to work. This is a transitional part of an ETL process so there is some redundancy.
I've got a table with two columns that are significant. The first field is a code value, the second is the foreign key associated with the look-up table that resolves the code. Using the code, I want to retrieve the foreign key value from the look-up table.
The problem is that the look-up table is a dimension that contains a flattened out hierarchy and the code is not at the lowest level of the hierarchy so a join on the code will return more than one row. I don't care which row I use. I just want the ID from the first row returned. I thought this would work but it isn't. I've tried a bunch of different things but I think this particular example demonstrates what I'm trying to do even if it isn't right. 🙂 Any suggestions?
UPDATE TempFactTable
SET CodeID = LookupID
FROM TempFactTable t, LookupTable lt
WHERE lt.LookupCode = t.LookupCode
AND lt.OtherCode = (SELECT MAX(lt.OtherCode) FROM LookupTabe lt
WHERE lt.LookupCode = t.LookupCode))
"Beliefs" get in the way of learning.
March 11, 2009 at 11:22 am
Try this:
UPDATE TempFactTable
SET CodeID = LookupID
FROM TempFactTable t
INNER JOIN (
SELECT LookupCode, MAX(LookupID) AS LookupID, MAX(OtherCode) AS OtherCode
FROM LookupTable
GROUP BY LookupCode
) AS lt
ON lt.LookupCode = t.LookupCode
AND lt.OtherCode = t.OtherCode
I don't know exactly what you're trying to do, but you could try to group or aggregate LookupID depending on its values related to LookupCode and OtherCode.
Regards
Gianluca
-- Gianluca Sartori
March 11, 2009 at 12:10 pm
Gianluca Sartori (3/11/2009)
Try this:
UPDATE TempFactTable
SET CodeID = LookupID
FROM TempFactTable t
INNER JOIN (
SELECT LookupCode, MAX(LookupID) AS LookupID, MAX(OtherCode) AS OtherCode
FROM LookupTable
GROUP BY LookupCode
) AS lt
ON lt.LookupCode = t.LookupCode
AND lt.OtherCode = t.OtherCode
I don't know exactly what you're trying to do, but you could try to group or aggregate LookupID depending on its values related to LookupCode and OtherCode.
Regards
Gianluca
Thanks for your help. Unfortunately, I'm getting an invalid column name on LookupID in line two.
Let me try a different way of explaining this.
I've got a fact with a LookupCode = 'AA' in one table. I've got the following entries in my look-up table.
ID LookUpCode OtherCode
1 'AA' 'Z1'
2 'AA' 'Z2'
3 'AA' 'Z3'
I don't care which ID of the three I get back. Any of them will do. Note, I do not have the value for OtherCode in my fact table. In other words, these facts are not at the lowest level of granularity in the hierarchy.
The issue I'm trying to solve is that the business has added a fifth level to a hierarchy. Unfortunately, my existing fact data only goes to the fourth level. Future fact data will be at the lower (fifth) level of granularity but I still need to mark the existing data with a valid ID from the new dimension.
This is really annoying me because it seems so simple yet I just can't get it to work. :w00t:
"Beliefs" get in the way of learning.
March 11, 2009 at 2:08 pm
Hi
Here a little example:
[font="Courier New"]DECLARE @lookup TABLE (id INT, code VARCHAR(100))
DECLARE @facts TABLE (code VARCHAR(100), lookup_id INT)
INSERT INTO @lookup VALUES (1, 'a')
INSERT INTO @lookup VALUES (2, 'a')
INSERT INTO @lookup VALUES (3, 'b')
INSERT INTO @lookup VALUES (4, 'b')
INSERT INTO @facts VALUES ('a', NULL)
INSERT INTO @facts VALUES ('b', NULL)
UPDATE f SET f.lookup_id = l.id
FROM @facts f
JOIN (SELECT MIN(id) id, code FROM @lookup GROUP BY code) l
ON f.code = l.code
SELECT * FROM @facts
[/font]
Greets
Flo
March 11, 2009 at 2:22 pm
Here's how I'd handle that:
;with CTE
(Row, LookupCode, LookupID)
as
(select
row_number() over (partition by LookupCode order by othercode),
LookupCode,
LookupID
from
dbo.LookupTable)
UPDATE TempFactTable
SET CodeID = LookupID
FROM TempFactTable t
inner join CTE
on t.LookupCode = CTE.LookupCode
and Row = 1;
This assumes you're using SQL 2005 or later, based on the forum you posted in.
Try it out, let me know if you have any questions.
Should be pretty fast, probably faster than the correlated sub-queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2009 at 7:13 am
Thanks for everyone's suggestions. As it turned out, it was embarrassingly simple and, as sometimes happens, I was trying to make it harder than it needed to be.
UPDATE TempFactTable
SET CodeID = (SELECT MAX(CodeID) AS CodeID
FROM LookupTable lt
WHERE lt.LookupCode = TempFactTable.LookupCode)
"Beliefs" get in the way of learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply