OK, I feel like an idiot because I'm sure the answer to this is so obvious that I'll kick myself when one of you tells me.SQL Server 2005. Here's my query:ALTER VIEW [R9].[vw_ITXXXX_176590OrgData]AS SELECT SRC.[RecordID], SRC.[Data Source ID], SRC.[GPID], HRA.[Last_Nm] AS [Employee Last Name], HRA.[First_Nm] AS [Employee First Name], SRC.[Externally Assigned Position ID], SRC.[Employee_Name], SRC.[Data_Source_Code], SRC.[Manager_GPID], SRC.[Manager_Name], ADDR.[Country Key] AS [Manager_Country], SRC.[Employee_Job_Code], SRC.[Employee_Location], SRC.[Employee_Country], SRC.[Employee_Cost_Center], SRC.[Employee_CDS_Level2], SRC.[Employee_CDS_Level3], SRC.[Employee_CDS_Level4], SRC.[Employee_CDS_Level8], SRC.[Employee_CDS_Level9], SRC.[Employee_Job_Family], SRC.[Employee_Type], SRC.[Employee_Comp_Freq], SRC.[Employee_FTPT], SRC.[Employee_RegTemp], SRC.[Employee_FLSA_Status], SRC.[Employee_Expat_Ind], SRC.[Employee_BandLevel], SRC.[Employee_Estab], SRC.[Employee_Union], SRC.[Employee_Bargaining_Unit], SRC.[Employee_Tax_Company], SRC.[Primary_HRA_GPID], SRC.[Secondary_HRA_GPID], SRC.[Success Factors HRG], SRC.[Divisional Comp Person1], SRC.[Divisional Comp Person2], SRC.[Divisional Comp Person3], SRC.[Divisional Comp Person4], SRC.[Divisional Comp Person5], SRC.[Divisional Comp Person6], SRC.[Divisional Comp Person7], SRC.[Divisional Comp Person8], SRC.[Divisional Comp Person9], SRC.[Divisional Comp Person10], HRA.[CRRNT_PRMRY_HRA_GPID] AS [Primary GPID], HRA.[PRMRY_HRA_LAST_NM] AS [Primary Last Name], HRA.[PRMRY_HRA_FIRST_NM] AS [Primary First Name], HRA.[CRRNT_SCNDRY_HRA_GPID] AS [Secondary GPID], HRA.[SCNDRY_HRA_LAST_NM] AS [Secondary Last], HRA.[SCNDRY_HRA_FIRST_NM] AS [Secondary First], SRC.GPID_Count, ISO.ISO2 AS [CountryCheck], Trans1.[Union Code] AS [UnionCheck], Trans2.[Barg Unit] AS [BargUnitCheck]FROM ( SELECT CDS.[RecordID], CDS.[Data Source ID], CDS.[Employee_GPID] AS [GPID], CDS.[Externally Assigned Position ID], CDS.[Employee_Name], CDS.[Data_Source_Code], CDS.[Manager_GPID], CDS.[Manager_Name], CDS.[Employee_Job_Code], CDS.[Employee_Location], CDS.[Employee_Country], CDS.[Employee_Cost_Center], CDS.[Employee_CDS_Level2], CDS.[Employee_CDS_Level3], CDS.[Employee_CDS_Level4], CDS.[Employee_CDS_Level8], CDS.[Employee_CDS_Level9], CDS.[Employee_Job_Family], CDS.[Employee_Type], CDS.[Employee_Comp_Freq], CDS.[Employee_FTPT], CDS.[Employee_RegTemp], CDS.[Employee_FLSA_Status], CDS.[Employee_Expat_Ind], CDS.[Employee_BandLevel], CDS.[Employee_Estab], CDS.[Employee_Union], CDS.[Employee_Bargaining_Unit], CDS.[Employee_Tax_Company], CDS.[Primary_HRA_GPID], CDS.[Secondary_HRA_GPID], CDS.[Success Factors HRG], CDS.[Divisional Comp Person1], CDS.[Divisional Comp Person2], CDS.[Divisional Comp Person3], CDS.[Divisional Comp Person4], CDS.[Divisional Comp Person5], CDS.[Divisional Comp Person6], CDS.[Divisional Comp Person7], CDS.[Divisional Comp Person8], CDS.[Divisional Comp Person9], CDS.[Divisional Comp Person10], COUNT(*) OVER (Partition BY [Employee_GPID]) AS 'GPID_COUNT' FROM R9.src_ITXXXX_CDS_176590OrgData CDS ) AS SRCINNER JOIN r9.src_IT0032_CDS_176573SourceSystem SOURCE ON LTRIM(RTRIM(SOURCE.[GPID])) = LTRIM(RTRIM(SRC.[GPID])) AND LEFT(SOURCE.[Source System],9) = LTRIM(RTRIM(SRC.[Data_Source_Code]))--THIS IS THE PROBLEM JOIN!!INNER JOIN R9.tb_IT0006_176562Address ADDR on LTRIM(RTRIM(SRC.[Manager_GPID])) = LTRIM(RTRIM(ADDR.[GPID])) and ADDR.[Address Type] = 1LEFT OUTER JOIN R9.tb_HRAMaster HRA ON HRA.[EMPL_ID] = SRC.GPID--Left outer join CountryCodes to check rule 14 and ensure Employee_Country matches 2 character ISO codeLEFT OUTER JOIN R9.ISO3166_CountryCodes ISO ON LTRIM(RTRIM(SRC.Employee_Country)) = ISO.ISO2--Left outer join translation table to check Employee UnionLEFT OUTER JOIN R9.trans_ITXXXX_176590OrgData Trans1 ON LTRIM(RTRIM(SRC.Employee_Union)) = LTRIM(RTRIM(Trans1.[Union Code]))--Left outer join translation table to check Employee Bargaining UnitLEFT OUTER JOIN R9.trans_ITXXXX_176590OrgData Trans2 ON LTRIM(RTRIM(SRC.Employee_Bargaining_Unit)) = LTRIM(RTRIM(Trans2.[Barg Unit]))
The problem is, when I look at the view it has two identical rows for each record. I've tracked it down to this join:INNER JOIN R9.tb_IT0006_176562Address ADDR on LTRIM(RTRIM(SRC.[Manager_GPID])) = LTRIM(RTRIM(ADDR.[GPID])) and ADDR.[Address Type] = 1
Any help on getting just one of each row would be appreciated! 
Query creating duplicate rows in view
Posted - 2010-03-01 : 12:56:10
