locked
Parent/Child Self ID on same table RRS feed

  • Question

  • Hi All

    I can't seem to get all results to return. I have a country table with a self referencing identifier 

    Here is the create/insert code:

    I tried all kinds of ways to get the full dataset to return: here is my query:

    USE [XXXX] go
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [data].[dim_Location](
    	[PK_LocationID] [int] IDENTITY(0,1) NOT NULL,
    	[BK_Location] [varchar](5) NOT NULL,
    	[LocationName] [varchar](30) NULL,
    	[GP_LocationName] [varchar](67) NULL,
    	[FK_Parent_LocationID] [int] NULL,
    	[BK_Location_CRM] [uniqueidentifier] NULL,
    	[CRM_Table] [varchar](1) NULL,
    	[CreateDate] [datetime] NULL,
    	[UpdateDate] [datetime] NULL,
    	[FK_BuildID] [int] NULL,
     CONSTRAINT [PK_LocationID] PRIMARY KEY CLUSTERED 
    (
    	[PK_LocationID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [data].[dim_Location] ON 
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (-1, N'U', N'Unknown Location', N'Unknown GP Location', -1, N'00000000-0000-0000-0000-000000000000', N'U', CAST(N'1900-01-01T00:00:00.000' AS DateTime), CAST(N'1900-01-01T00:00:00.000' AS DateTime), -1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (0, N'AFRIC', N'Africa', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.173' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (1, N'ASIAP', N'Asia Pacific', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.177' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (2, N'AUSTR', N'Australasia', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.177' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (3, N'CAN', N'Canada', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.177' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (4, N'EUROP', N'Europe', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.180' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (5, N'LATIN', N'Latin America', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.180' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (6, N'USC', N'USA / Canada / Caribbean', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.180' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (7, N'WRLDW', N'Worldwide', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.180' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (8, N'002', N'Various', NULL, NULL, NULL, N'T', CAST(N'2015-10-12T07:24:25.183' AS DateTime), CAST(N'2015-10-12T07:24:25.093' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (9, N'AFRI', N'Africa', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.047' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (10, N'AMERI', N'Americas', NULL, 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.050' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (11, N'AMXNA', N'Americas excluding N America', NULL, 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.050' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (12, N'ASIAC', N'Asia', N'1729', 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.053' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (13, N'ASIXJ', N'Asia excluding Japan', NULL, 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.053' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (14, N'AUSNZ', N'Australia and New Zealand', N'1635', 2, NULL, N'C', CAST(N'2015-10-12T07:24:26.053' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (15, N'AUSTO', N'Australsia / Oceania', N'1635', 2, NULL, N'C', CAST(N'2015-10-12T07:24:26.057' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (16, N'CAMER', N'Central America', N'1519', 5, NULL, N'C', CAST(N'2015-10-12T07:24:26.057' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (17, N'CASIA', N'Central Asia', NULL, 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.057' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (18, N'CHNIS', N'Channel Islands', NULL, 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.060' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (19, N'CONTE', N'Continental Europe', N'1248', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.060' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (20, N'DENFA', N'Denmark / Faroe Islands', N'1249', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.060' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (21, N'EAFRI', N'Eastern Africa', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.060' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (22, N'EASIA', N'Eastern Asia', NULL, 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.063' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (23, N'EEUR', N'Eastern Europe', N'1250', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.070' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (24, N'EUJUC', N'Europe/Japan/USA/Canada', N'1905', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.073' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (25, N'EUR', N'Europe', N'1251', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.073' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (26, N'EURAM', N'Europe & Americas', N'1901', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.077' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (27, N'EURJP', N'Europe / Japan', N'1902', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.077' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (28, N'EURJU', N'Europe / Japan / United States', N'1903', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.077' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (29, N'EURUS', N'Europe / United States', N'1904', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.080' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (30, N'EUUSC', N'Europe/United States/Canada', N'1906', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.080' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (31, N'FRGUK', N'France / Germany / United King', N'1252', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.080' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (32, N'FRIUK', N'France/Ireland/United Kingdom', N'1254', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.080' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (33, N'FRUK', N'France / United Kingdom', N'1253', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.090' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (34, N'GERSW', N'Germany/Switzerland', N'1257', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.090' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (35, N'GERUK', N'Germany / United Kingdon', N'1256', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.093' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (36, N'INDIA', N'India', N'1706', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.093' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (37, N'IRLUK', N'Ireland / United Kingdom', N'1258', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.093' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (38, N'ITPIS', N'Italy/Portugal/Israel', N'1259', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.093' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (39, N'JAPUC', N'Japan / United States / Canada', N'1908', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.100' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (40, N'JPNUS', N'Japan / United States', N'1907', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.100' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (41, N'LATAM', N'Latin America', N'1520', 5, NULL, N'C', CAST(N'2015-10-12T07:24:26.100' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (42, N'MAFRI', N'Middle Africa', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.103' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (43, N'MELAN', N'Melanesia', NULL, 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.107' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (44, N'MEXIC', N'Mexico', N'1102', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.110' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (45, N'NAFRI', N'Northern Africa', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.113' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (46, N'NAMER', N'Northern America', NULL, 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.113' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (47, N'NEURO', N'Northern Europe', N'1251', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.117' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (48, N'OCEAN', N'Oceania', N'1634', 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.117' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (49, N'OCXAN', N'Oceania excl Aus and New Zeala', NULL, 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.120' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (50, N'PALES', N'State of Palestine', N'1317', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.120' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (51, N'SAFRI', N'Southern Africa', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.120' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (52, N'SAMER', N'South America', N'1521', 5, NULL, N'C', CAST(N'2015-10-12T07:24:26.123' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (53, N'SARK', N'Sark', NULL, 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.123' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (54, N'SASIA', N'Southern Asia', NULL, 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.127' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (55, N'SCAND', N'Scandinavia', N'1260', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.130' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (56, N'SEASI', N'South-Eastern Asia', NULL, 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.130' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (57, N'SEURO', N'Southern Europe', NULL, 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.133' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (58, N'SPAIN', N'Spain', N'1261', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.133' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (59, N'SSUDA', N'South Sudan', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.137' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (60, N'USA', N'United States', N'2000', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.137' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (61, N'USACC', N'United States/Canada/Caribbean', N'2002', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.140' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (62, N'USACN', N'United States / Canada', N'2001', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.143' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (63, N'USANZ', N'US/Australia/NZ', NULL, 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.143' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (64, N'WAFRI', N'Western Africa', NULL, 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.147' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (65, N'WASIA', N'Western Asia', NULL, 1, NULL, N'C', CAST(N'2015-10-12T07:24:26.147' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (66, N'WEUR', N'Western Europe', N'1262', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.150' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (67, N'003', N'Caribbean', N'1430', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.150' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (68, N'004', N'Afghanistan', N'1300', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.150' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (69, N'006', N'St. Thomas', N'1423', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.150' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (70, N'007', N'St. Croix', N'1420', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.157' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (71, N'008', N'Albania', N'1200', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.160' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (72, N'009', N'Worldwide', N'1909', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.160' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (73, N'010', N'Antarctica', N'1900', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.160' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (74, N'011', N'Worldwide Ex US & Japan', N'1912', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.163' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (75, N'012', N'Algeria', N'1301', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.163' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (76, N'013', N'Wordlwide Excluding Japan', N'1910', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.167' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (77, N'014', N'Worldwide Excluding USA', N'1911', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.167' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (78, N'015', N'Wwide Ex US/Canada', N'1911', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.170' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (79, N'016', N'American Samoa', N'1600', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.170' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (80, N'020', N'Andorra', N'1218', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.170' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (81, N'024', N'Angola', N'1800', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.173' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (82, N'028', N'Antigua and Barbuda', N'1401', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.177' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (83, N'031', N'Azerbaijan', N'1302', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.177' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (84, N'032', N'Argentina', N'1500', 5, NULL, N'C', CAST(N'2015-10-12T07:24:26.177' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (85, N'036', N'Australia', N'1601', 2, NULL, N'C', CAST(N'2015-10-12T07:24:26.180' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (86, N'040', N'Austria', N'1220', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.180' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (87, N'044', N'Bahamas', N'1425', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.180' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (88, N'048', N'Bahrain', N'1303', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.183' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (89, N'050', N'Bangladesh', N'1700', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.183' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (90, N'051', N'Armenia', N'1219', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.187' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (91, N'052', N'Barbados', N'1403', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.187' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (92, N'056', N'Belgium', N'1221', 4, NULL, N'C', CAST(N'2015-10-12T07:24:26.190' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (93, N'060', N'Bermuda', N'1101', 8, NULL, N'C', CAST(N'2015-10-12T07:24:26.190' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (94, N'064', N'Bhutan', N'1701', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.193' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (95, N'068', N'Bolivia', N'1501', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.193' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (96, N'070', N'Bosnia and Herzegowina', N'1202', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.197' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (97, N'072', N'Botswana', N'1802', 0, NULL, N'C', CAST(N'2015-10-12T07:24:26.197' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (98, N'074', N'Bouvet Island', N'1602', 7, NULL, N'C', CAST(N'2015-10-12T07:24:26.200' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (99, N'076', N'Brazil', N'1502', 5, NULL, N'C', CAST(N'2015-10-12T07:24:26.207' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    INSERT [data].[dim_Location] ([PK_LocationID], [BK_Location], [LocationName], [GP_LocationName], [FK_Parent_LocationID], [BK_Location_CRM], [CRM_Table], [CreateDate], [UpdateDate], [FK_BuildID]) VALUES (100, N'084', N'Belize', N'1404', 6, NULL, N'C', CAST(N'2015-10-12T07:24:26.210' AS DateTime), CAST(N'2015-10-12T07:24:25.807' AS DateTime), 1)
    GO
    [data].[dim_Location] OFF
    GO
    ALTER TABLE [data].[dim_Location] ADD  DEFAULT (getdate()) FOR [CreateDate]
    GO

    I tried all kinds of ways to get the full dataset to return: here is my query:

      ;with SourceData0 as(   Select 0 as Heirarchy, PK_LocationID, BK_Location, LocationName, FK_Parent_LocationID     from [TMR_DWH_LIVE_NewETL].[cubeview].[vw_Dim_Location]             where CRM_Table = 'U'),             SourceData1 as(   Select 1 as Heirarchy, PK_LocationID, BK_Location, LocationName, FK_Parent_LocationID   from [TMR_DWH_LIVE_NewETL].[cubeview].[vw_Dim_Location]   where CRM_Table = 'T'),             SourceData2 as(               Select 2 as Heirarchy, PK_LocationID, BK_Location, LocationName, FK_Parent_LocationID   from [TMR_DWH_LIVE_NewETL].[cubeview].[vw_Dim_Location]   where CRM_Table = 'C'),   SourceData3 as(     Select 3 as Heirarchy, PK_LocationID, BK_Location, LocationName, FK_Parent_LocationID   from [TMR_DWH_LIVE_NewETL].[cubeview].[vw_Dim_Location]   where CRM_Table = 'S')   Select   Coalesce(S3.PK_LocationID,S2.PK_LocationID,S1.PK_LocationID),   S1.LocationName as Continent,    S2.LocationName as Country,    S3.LocationName as [State]    FROM SourceData1 S1   LEFT JOIN SourceData2 S2 on S1.PK_LocationID = S2.FK_Parent_LocationID   LEFT JOIN SourceData3 S3 on S2.PK_LocationID = S3.FK_Parent_LocationID              order by Coalesce(S3.PK_LocationID,S2.PK_LocationID,S1.PK_LocationID)


    The problem is in my result set i get missing rows, specifically ID 60 is missing but can be used in the other tables as a reference

    How can i fix the query to return all the country combinations?

    Thanks in advance :) Much appreciated 

    J




    • Edited by James OHara Thursday, November 21, 2019 4:17 PM a
    Thursday, November 21, 2019 3:36 PM

Answers

  • ;with mycte as
    (   Select CRM_Table, 0 as Heirarchy
    , PK_LocationID, BK_Location, LocationName
    , LocationName as LocationName2
    , FK_Parent_LocationID     
    from  [data].[Dim_Location]
    where FK_Parent_LocationID is   null
    
    union all
    
    
    Select l.CRM_Table, m.Heirarchy+1 Heirarchy,
    l.PK_LocationID, l.BK_Location, l.LocationName, m.LocationName2
    , l.FK_Parent_LocationID   
    from mycte  m 
    join [data].[Dim_Location] l 
    on m.[PK_LocationID]=l.[FK_Parent_LocationID]
    
    
    )
    
    select * from mycte
    order by PK_LocationID
    

    Thursday, November 21, 2019 4:42 PM

All replies

  • ;with mycte as
    (   Select CRM_Table, 0 as Heirarchy
    , PK_LocationID, BK_Location, LocationName
    , LocationName as LocationName2
    , FK_Parent_LocationID     
    from  [data].[Dim_Location]
    where FK_Parent_LocationID is   null
    
    union all
    
    
    Select l.CRM_Table, m.Heirarchy+1 Heirarchy,
    l.PK_LocationID, l.BK_Location, l.LocationName, m.LocationName2
    , l.FK_Parent_LocationID   
    from mycte  m 
    join [data].[Dim_Location] l 
    on m.[PK_LocationID]=l.[FK_Parent_LocationID]
    
    
    )
    
    select * from mycte
    order by PK_LocationID
    

    Thursday, November 21, 2019 4:42 PM
  • I think you need to start over and desperately need to read a book on basic data modeling. The identity table property can never be used as a primary key by definition. It's not really a column is just how non-RDBMS programmers fake magnetic tape in the 21st century. Your prefixes violate the basic rule of never mixing data and metadata; things like "PK_" tell us how a data element is used in this particular situation, but not what it is by its nature. Since you don't do math on identifiers and other things in your schema should never be numeric. They are what we call a nominal scale. The adjacency list model of the hierarchy that you're trying to use is completely non-relational. The same data element appears in multiple places, depending whether it's a parent or child node in the tree traversal. Oh, we also don't like traversals because they are by their very nature procedural and this is a declarative language. We also don't include audit data, like those timestamps, in the table being audited. This means that when a row in the table is destroyed, then so is its audit history. Finally, you don't seem to know that we can insert a whole table with a values clause in SQL. You're still doing a row at a time. Insertions like we had to 50 years ago with Sybase and a model of punchcards for input.

    Since I don't have full specs, here's a skeleton to get you started. It uses the nested set model for hierarchies, and properly sets that separates out the elements from the relationship instead of blending them together as you did.

    CREATE TABLE Locations
    (location_id CHAR(10)  NOT NULL PRIMARY KEY,
      location_name VARCHAR(30) NOT NULL,
      gp_location_name VARCHAR(67);

    CREATE TABLE Location_Heirarchy 
    (location_id INTEGER NOT NULL 
      REFERENCES Locations (location_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
     rgt INTEGER NOT NULL CHECK (rgt >0) UNIQUE,
     lft  INTEGER NOT NULL CHECK (lft >rgt)  UNIQUE,

    >> The problem is in my result set I get missing rows,  ..<<

    I think you'll find the nested set model prevents a lot of the problems that the adjacency list causes.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, November 21, 2019 9:23 PM
  • Hi Thanks for the reply

    I didn't create the schema - i inherited it and now am not allowed to change it :-) 

    Thankyou for your help!

    Regards

    J

    Friday, November 22, 2019 8:27 AM