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 post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply