February 13, 2008 at 10:37 am
I'm running into a problem with the following query. I'm trying to create a 'helper' table to tie my sales fact table back to my vendor dimension. I'm taking the distinct store_num, upc_num and vend_ID combinations from the SHIP_Fact table and adding them to the Sales_Vend_Help table. Here's my query but it's not working the way I'd hope that it would with the where clauses at the end. If I run the select portion of the query without the where clauses I get around 750000 records. The helper table has ~250000 records in it from when I created it so I'm expecting it to add the 500000 new reords. Any help would be appreciated...
Insert Into Sales_Vend_Help (store_num, upc_num, vend_ID)
(select distinct b.store_num, c.upc_num, a.vend_ID
from SHIP_Fact a
inner join vw_dim_location b
on a.location_ID = b.location_ID
inner join dim_upc c
on a.upcID = c.upcID
where store_num not in (select distinct store_num from Sales_Vend_Help)
and upc_num not in (select distinct upc_num from Sales_Vend_Help)
and vend_ID not in (select distinct vend_ID from Sales_Vend_Help))
February 13, 2008 at 10:54 am
Can you post the DDL for the tables including keys/refererence? Your joins may be producing duplicate results.
Toni
February 13, 2008 at 11:05 am
CREATE TABLE [dbo].[SHIP_Fact] (
[dateID] [int] NULL ,
[vend_ID] [int] NULL ,
[upcID] [int] NULL ,
[pos_dept_ID] [int] NULL ,
[location_ID] [int] NULL ,
[ord_type_ID] [int] NULL ,
[out_rsn_ID] [int] NULL ,
[invc_nbr] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [numeric](11, 2) NULL ,
[wgt] [numeric](17, 5) NULL ,
[pack] [numeric](5, 0) NULL ,
[cost] [numeric](12, 4) NULL ,
[retail] [numeric](9, 4) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[master_location] (
[Location_ID] [int] IDENTITY (1, 1) NOT NULL ,
[storeRaw] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[store_Num] [float] NULL ,
[store_Desc] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District_Num] [float] NULL ,
[District_Desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[division_Num] [float] NULL ,
[division_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company_num] [float] NULL ,
[company_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[arena_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DIM_UPC] (
[upcID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[category_ID] [int] NOT NULL ,
[segment_num] [int] NULL ,
[segment_desc] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sub_Cat_num] [int] NULL ,
[sub_cat_desc] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[category_num] [int] NULL ,
[category_desc] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[maj_cat_num] [int] NULL ,
[maj_cat_desc] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upc_num] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_num] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SALES_Vend_Help] (
[store_num] [float] NULL ,
[upc_num] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vend_ID] [int] NULL
) ON [PRIMARY]
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply