November 9, 2012 at 5:18 pm
Hi, so I'm brand new when it comes to T-SQL.
I hope I can explain this for everyone to understand.
There is a table called Plant that has a list of different plants in the company by location, such as SW Kansas, Baker, West Texas, etc. There is one location mapped to "ALL" where for some reason some of the contracts are assigned. I need to find all of the contracts currently listed in "ALL" and assign them to an actual location.
I was originally told to use two outer join statements to the ContractToPlantMapping table and the Plant table, based on ContractKey but since the ContractToPlantMapping table doesn't have a ContractKey, I think I need to join the Contract table as well. Here's what I have:
FROM [Edw].[FACT].[SettleFee_WillTest] a
LEFT OUTER JOIN [Edw].[DIM].[Contract] b
ON a.[ContractKey] = b.[ContractKey]
LEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] c
ON b.[ContractNumber] = c.[ContractNumber]
LEFT OUTER JOIN [Edw].[DIM].[Plant] d
ON c.[PlantKey] = d.[PlantKey]
AND d.[PlantNumber] = 'ALL'
Then in the select statement I was thinking of:
CASE
WHEN d.[PlantNumber] = 'ALL' THEN c.[PlantKey]
ELSE d.[PlantNumber]
END AS PlantName
I keep getting NULLS in the new PlantName column from the SELECT statement and it doesn't seem like it's bringing over the data associated with the "ALL" plant.
It's a pretty simple problem, I just don't have any idea of what I'm doing.
Any tips would be greatly appreciated. Is the concept behind the joins correct? Let me know if you need more information.
November 11, 2012 at 6:17 pm
will.byrne (11/9/2012)
... I keep getting NULLS in the new PlantName column from the SELECT statement and it doesn't seem like it's bringing over the data associated with the "ALL" plant.
Left outer joins will include rows in the table in the [font="Courier New"]FROM[/font] even if there are no matching rows in the other tables you are joining to (unless rows in the other tables are being restricted in the [font="Courier New"]WHERE[/font] clause). I would guess you don't want "[font="Courier New"]AND d.[PlantNumber] = 'ALL'[/font]" as part of the LEFT OUTER JOIN condition. Instead replace the "AND" with a "WHERE" to make it a required restriction like this:
SELECT a.ContractKey, c.PlantKey, d.PlantNumber
FROM [Edw].[FACT].[SettleFee_WillTest] a
LEFT OUTER JOIN [Edw].[DIM].[Contract] b
ON a.[ContractKey] = b.[ContractKey]
LEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] c
ON b.[ContractNumber] = c.[ContractNumber]
LEFT OUTER JOIN [Edw].[DIM].[Plant] d
ON c.[PlantKey] = d.[PlantKey]
WHERE d.[PlantNumber] = 'ALL'
... or consider using just JOINs / INNER JOINs.
Of course have someone familar with your data model double check all this before you make an UPDATE!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply