r
information. An outer join and a union is used to insure I get all customer
s
and vendors within the selection criteria even though either entity may not
have sales. The query is:
SELECT
cs.customer_ident,
ss.Total_Cases,
ss.Total_Pounds,
ss.Total_Sales,
ss.Total_Actual,
ss.Total_Rep,
ss.Total_Profit,
ss.Total_Items,
cs.customer_num,
cs.customer_name,
cs.ship_to_address1,
cs.ship_to_county,
cs.ship_to_state,
cs.customer_phone1,
op.opco_desc,
ss.opco_vendor_num,
ss.opco_vendor_desc,
rp.salesrep_num,
rp.salesrep_name
FROM
dim_customer cs
LEFT OUTER JOIN
(SELECT
sh.customer_ident,
vn.opco_vendor_num,
vn.opco_vendor_desc,
vn.vendor_ident,
sum(sd.extended_cases) AS Total_Cases,
sum(sd.extended_sales) AS Total_Sales,
sum(sd.extended_cost) AS Total_Actual,
sum(sd.extended_sales_rep_cost) AS Total_Rep,
sum(sd.extended_pounds) AS Total_Pounds,
Count(DISTINCT sd.item_ident) As Total_Items,
sum(sd.extended_sales - sd.extended_Cost) As Total_Profit
FROM
fact_sales_header sh
INNER JOIN
fact_sales_detail sd
ON
sh.header_ident = sd.header_ident
INNER JOIN
dim_item it
ON
sd.item_ident = it.item_ident
INNER JOIN
dim_time tm
ON
sh.time_ident = tm.time_ident
INNER JOIN
dim_vendor vn
ON
it.vendor_ident = vn.vendor_ident
INNER JOIN
dim_customer cs1
ON
cs1.customer_ident = sh.customer_ident
INNER JOIN
dim_salesRep rp1
ON
cs1.sales_rep_ident = rp1.salesRep_ident
WHERE
(((tm.gl_year = 2004 AND tm.gl_period >= 4) OR tm.gl_year > 2004) and
((tm.gl_year =2005 AND tm.gl_period <=3) OR tm.gl_year < 2005)) AND
sh.opco_num = 125
and it.subCategory_num in (3932)
and cs1.abc_rating = 'A'
GROUP BY
vn.vendor_ident,
vn.opco_vendor_num,
sh.customer_ident,
vn.opco_vendor_desc
) ss
ON
cs.customer_ident = ss.customer_ident
INNER JOIN
dim_opco op
ON
cs.opco_num = op.opco_num
INNER JOIN
dim_salesRep rp
ON
cs.sales_rep_ident = rp.salesRep_ident
WHERE
cs.opco_num = 125 AND NOT exists
(SELECT
sh1.customer_ident,
sum(sd1.extended_cases) AS Cases_Test
FROM
fact_sales_header sh1
INNER JOIN
fact_sales_detail sd1
ON
sh1.header_ident = sd1.header_ident
INNER JOIN
dim_item it1
ON
sd1.item_ident = it1.item_ident
INNER JOIN
dim_time tm1
ON
sh1.time_ident = tm1.time_ident WHERE
sh1.customer_ident = cs.customer_ident
AND (((tm1.gl_year = 2004 AND tm1.gl_period >= 4) OR tm1.gl_year > 2004)
and ((tm1.gl_year =2005 AND tm1.gl_period <=3) OR tm1.gl_year < 2005)) AND
sh1.opco_num = 125
and it1.subCategory_num in (3932)
GROUP BY
sh1.customer_ident
HAVING
sum(sd1.extended_cases) > 10000)
and cs.abc_rating = 'A'
UNION ALL
SELECT Distinct
NULL as customer_ident,
NULL as Total_Cases,
NULL as Total_Pounds,
NULL as Total_Sales,
NULL as Total_Actual,
NULL as Total_Rep,
NULL as Total_Profit,
NULL as Total_Items,
NULL as customer_num,
NULL as customer_name,
NULL as ship_to_address1,
NULL as ship_to_county,
NULL as ship_to_state,
NULL as customer_phone1,
op.opco_desc,
vn.opco_vendor_num,
vn.opco_vendor_desc,
NULL as salesrep_num,
NULL as salesrep_name
FROM
dim_item it
INNER JOIN
dim_vendor vn
ON
it.vendor_ident = vn.vendor_ident
INNER JOIN
dim_opco op
ON
it.opco_num = op.opco_num
WHERE
it.opco_num = 125
and it.subCategory_num in (3932)
ORDER BY
cs.customer_ident,
opco_vendor_desc,
salesRep_name,
salesRep_num,
customer_num,
customer_name
Even though the result set from the left outer join query produces rows with
customer idents that match customer idents from the main select statement,
the sales data is not joined in (It looks like the query from the LEFT OUTER
JOIN returned no matching rows).
The interesting thing is if I comment out or remove the UNION ALL portion of
the query, the LEFT OUTER JOIN seems to work as I would expect.
I also change the LEFT OUTER JOIN to a FULL OUTER JOIN but did not make any
difference.
Does anyone have any idea what is happening here? Below are the DDL
statements for the tables involved:
CREATE TABLE [fact_sales_header] (
[header_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[time_ident] [int] NOT NULL ,
[customer_ident] [int] NOT NULL ,
[order_source] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credit_invoice_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[invoice_count] [int] NOT NULL ,
[invoice_line_count] [int] NOT NULL ,
[routed_orders] [int] NOT NULL ,
[published_ident] [int] NOT NULL ,
PRIMARY KEY CLUSTERED
(
[header_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_fact_sales_header_01] UNIQUE NONCLUSTERED
(
[opco_num],
[time_ident],
[customer_ident],
[order_source],
[credit_invoice_flag]
) ON [PRIMARY] ,
FOREIGN KEY
(
[customer_ident]
) REFERENCES [dim_customer] (
[customer_ident]
),
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[published_ident]
) REFERENCES [meta_published] (
[published_ident]
),
FOREIGN KEY
(
[time_ident]
) REFERENCES [dim_time] (
[time_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [fact_sales_detail] (
[detail_ident] [int] IDENTITY (1, 1) NOT NULL ,
[header_ident] [int] NOT NULL ,
[item_ident] [int] NOT NULL ,
[original_item_ident] [int] NULL ,
[special_handling_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[credit_ident] [int] NULL ,
[extended_sales] [numeric](15, 2) NULL ,
[extended_cost] [numeric](15, 2) NULL ,
[extended_sales_rep_cost] [numeric](15, 2) NULL ,
[extended_cases] [numeric](19, 5) NULL ,
[extended_pounds] [numeric](15, 2) NULL ,
[market_cost] [numeric](15, 4) NULL ,
[invoice_line_count] [int] NOT NULL ,
CONSTRAINT [pk_fact_sales_detail] PRIMARY KEY NONCLUSTERED
(
[detail_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[credit_ident]
) REFERENCES [dim_creditCode] (
[credit_ident]
),
FOREIGN KEY
(
[header_ident]
) REFERENCES [fact_sales_header] (
[header_ident]
),
FOREIGN KEY
(
[item_ident]
) REFERENCES [dim_item] (
[item_ident]
),
FOREIGN KEY
(
[original_item_ident]
) REFERENCES [dim_item] (
[item_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_item] (
[item_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[opco_item_num] [int] NOT NULL ,
[item_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__dim_item__item_d__15A53433] DEFAULT ('Unknown'),
[item_desc2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__item_d__1699586C] DEFAULT ('Unknown'),
[category_num] [numeric](4, 0) NOT NULL CONSTRAINT
[DF__dim_item__catego__178D7CA5] DEFAULT (0),
[category_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__catego__1881A0DE] DEFAULT ('Unknown'),
[subCategory_num] [numeric](4, 0) NOT NULL CONSTRAINT
[DF__dim_item__subCat__1975C517] DEFAULT (0),
[subCategory_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__subCat__1A69E950] DEFAULT ('Unknown'),
[brand_num] [numeric](5, 0) NOT NULL CONSTRAINT
[DF__dim_item__brand___1B5E0D89] DEFAULT (0),
[brand_key_word] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__brand___1C5231C2] DEFAULT ('Unknown'),
[brand_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__brand___1D4655FB] DEFAULT ('Unknown'),
[brand_type_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__brand___1E3A7A34] DEFAULT ('Unknown'),
[brand_type_num] [numeric](2, 0) NOT NULL CONSTRAINT
[DF__dim_item__brand___1F2E9E6D] DEFAULT (0),
[vendor_ident] [int] NULL CONSTRAINT [DF__dim_item__vendor__2116E6DF]
DEFAULT (0),
[pack] [numeric](4, 0) NULL ,
[size] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[common_item_num] [numeric](6, 0) NOT NULL CONSTRAINT
[DF__dim_item__common__220B0B18] DEFAULT (0),
[non_inv_item_code_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__dim_item__non_in__22FF2F51] DEFAULT (''),
[common_item_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__dim_item__common__3AD6B8E2] DEFAULT ('Unknown'),
PRIMARY KEY CLUSTERED
(
[item_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[vendor_ident]
) REFERENCES [dim_vendor] (
[vendor_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_time] (
[time_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NULL ,
[gl_w
] [int] NOT NULL ,[gl_period] [int] NOT NULL ,
[gl_quarter] [int] NOT NULL ,
[gl_year] [int] NOT NULL ,
[begin_w
_date] [datetime] NULL ,PRIMARY KEY CLUSTERED
(
[time_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_time_01] UNIQUE NONCLUSTERED
(
[opco_num],
[gl_w
],[gl_year]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_vendor] (
[vendor_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[opco_vendor_num] [int] NOT NULL ,
[opco_vendor_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_vendo__opco___11D4A34F] DEFAULT ('Unknown'),
PRIMARY KEY CLUSTERED
(
[vendor_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_vendor_01] UNIQUE NONCLUSTERED
(
[opco_num],
[opco_vendor_num]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_customer] (
[customer_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL CONSTRAINT [DF__dim_custo__opco___73501C2F]
DEFAULT (0),
[customer_num] [int] NOT NULL ,
[customer_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__custo__74444068] DEFAULT ('Unknown'),
[sales_rep_ident] [int] NOT NULL CONSTRAINT
[DF__dim_custo__sales__762C88DA] DEFAULT (0),
[abc_rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__abc_r__7720AD13] DEFAULT ('0'),
[type_num] [int] NOT NULL CONSTRAINT [DF__dim_custo__type___7814D14C]
DEFAULT (0),
[type_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__type___7908F585] DEFAULT ('Unknown'),
[major_type_num] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__major__79FD19BE] DEFAULT (0),
[major_type_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__major__7AF13DF7] DEFAULT ('Unknown'),
[chain_code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__chain__7BE56230] DEFAULT ('INDEP'),
[chain_code_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__chain__7CD98669] DEFAULT ('Not a Chain'),
[chain_code_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__chain__7DCDAAA2] DEFAULT ('U'),
[ship_to_county] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__ship___7EC1CEDB] DEFAULT ('Unknown'),
[ship_to_state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__ship___7FB5F314] DEFAULT ('Un'),
[ship_to_address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__ship___00AA174D] DEFAULT ('Unknown'),
[customer_phone1] [numeric](10, 0) NOT NULL CONSTRAINT
[DF__dim_custo__custo__019E3B86] DEFAULT (0),
PRIMARY KEY CLUSTERED
(
[customer_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_customer_01] UNIQUE NONCLUSTERED
(
[opco_num],
[customer_num]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[sales_rep_ident]
) REFERENCES [dim_salesRep] (
[salesRep_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_salesRep] (
[salesRep_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[salesRep_num] [int] NOT NULL ,
[salesRep_name] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_sales__sales__6ABAD62E] DEFAULT ('Unknown'),
[salesManager_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6BAEFA67] DEFAULT ('Unk'),
[salesManager_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6CA31EA0] DEFAULT ('Unknown'),
[salesTerritory] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6D9742D9] DEFAULT ('Unk'),
[salesTerritory_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__dim_sales__sales__6E8B6712] DEFAULT ('Unknown'),
[Rep_Net_Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mgr_Net_Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[salesRep_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_opco] (
[opco_num] [int] NOT NULL ,
[opco_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[region_num] [int] NOT NULL ,
[region_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Dim_OPCO] PRIMARY KEY CLUSTERED
(
[opco_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GOHere is a suggestion that may solve your problem: move the two INNER
JOIN-s (with dim_opco and with dim_salesRep) BEFORE the LEFT JOIN.
Razvan|||Thanks for the suggestion. However, moving the INNER JOINS up made no
effect on the result set.
"Razvan Socol" wrote:
> Here is a suggestion that may solve your problem: move the two INNER
> JOIN-s (with dim_opco and with dim_salesRep) BEFORE the LEFT JOIN.
> Razvan
>sql
No comments:
Post a Comment