hi all
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin
declare @.current_date datetime
set @.current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @.current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc
endTry using UNION ALL.
-PatP
Showing posts with label union. Show all posts
Showing posts with label union. Show all posts
Wednesday, March 28, 2012
Query with union no longer updatable in SQL 2005
I have a query that uses a union that was updatable in SQL 2000 but not SQL
2005
Here is a simple example of a result set that is updatable in SQL 2000 but
not SQL 2005
Northwind DB
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'ALFKI')
UNION ALL
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'ANATR')Richard
Can you show us your UPDATE statement?
"Richard Brown" <RichardBrown@.discussions.microsoft.com> wrote in message
news:C5D146D9-CA8E-4984-8417-08BEFCFE3816@.microsoft.com...
>I have a query that uses a union that was updatable in SQL 2000 but not SQL
> 2005
> Here is a simple example of a result set that is updatable in SQL 2000 but
> not SQL 2005
> Northwind DB
> SELECT CustomerID, CompanyName, ContactName
> FROM Customers
> WHERE (CustomerID = 'ALFKI')
>
> UNION ALL
> SELECT CustomerID, CompanyName, ContactName
> FROM Customers
> WHERE (CustomerID = 'ANATR')|||The problem was discussed in MSDN Product Feedback Center in the
following bug report (which was closed with the resolution "by
design"):
http://lab.msdn.microsoft.com/produ...59-17b0073cfa26
Razvan
2005
Here is a simple example of a result set that is updatable in SQL 2000 but
not SQL 2005
Northwind DB
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'ALFKI')
UNION ALL
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = 'ANATR')Richard
Can you show us your UPDATE statement?
"Richard Brown" <RichardBrown@.discussions.microsoft.com> wrote in message
news:C5D146D9-CA8E-4984-8417-08BEFCFE3816@.microsoft.com...
>I have a query that uses a union that was updatable in SQL 2000 but not SQL
> 2005
> Here is a simple example of a result set that is updatable in SQL 2000 but
> not SQL 2005
> Northwind DB
> SELECT CustomerID, CompanyName, ContactName
> FROM Customers
> WHERE (CustomerID = 'ALFKI')
>
> UNION ALL
> SELECT CustomerID, CompanyName, ContactName
> FROM Customers
> WHERE (CustomerID = 'ANATR')|||The problem was discussed in MSDN Product Feedback Center in the
following bug report (which was closed with the resolution "by
design"):
http://lab.msdn.microsoft.com/produ...59-17b0073cfa26
Razvan
Query with union
Hello Group!
I submmit a SP with a update query that use multiple UNION.
This SP fire multiple sub-threads for the same SPID and fail with deadlock condition.
There is a way for suppress that sub-threads ?
TIA,
Aldair.Need more info on your sp. I'd be leary about running update queries against Union joins. You might be better off using your union query to create a temporary table or table variable that holds the index keys of the values you want to update, and then update your values in a second step.
blindman|||The threads shoukd all enlist in the same transaction and not cause a deadlock on it's own.
If you think it is causing a problem the try it with maxdop=1|||Thanks for all.
My update comand that has a where condition with several UNIONs fail when run at the production server (more than one CPU) but it work fine at test server (single CPU). This look like a bug!
When I use the OPTION clause MAXDOP=1, it work at the production box.
Thank you for help.
Aldair.|||Do you have the latest service pack?
There have been a number of bugs fixed to do with this.|||Yes, I do.
Microsoft SQL Server 2000 - 8.00.760
This is the SP 3, isnt it ?
We use the SQL Profiler to see the procedures steps and during the update the SQL Server build a multi-thread plan (we can see it using SP_WHO: SPID= n, ECID= 0,1,2,3,4), and at this point the procedure failed with error 1205 (Dead Lock victim).
Thank you for your help.
Aldair
I submmit a SP with a update query that use multiple UNION.
This SP fire multiple sub-threads for the same SPID and fail with deadlock condition.
There is a way for suppress that sub-threads ?
TIA,
Aldair.Need more info on your sp. I'd be leary about running update queries against Union joins. You might be better off using your union query to create a temporary table or table variable that holds the index keys of the values you want to update, and then update your values in a second step.
blindman|||The threads shoukd all enlist in the same transaction and not cause a deadlock on it's own.
If you think it is causing a problem the try it with maxdop=1|||Thanks for all.
My update comand that has a where condition with several UNIONs fail when run at the production server (more than one CPU) but it work fine at test server (single CPU). This look like a bug!
When I use the OPTION clause MAXDOP=1, it work at the production box.
Thank you for help.
Aldair.|||Do you have the latest service pack?
There have been a number of bugs fixed to do with this.|||Yes, I do.
Microsoft SQL Server 2000 - 8.00.760
This is the SP 3, isnt it ?
We use the SQL Profiler to see the procedures steps and during the update the SQL Server build a multi-thread plan (we can see it using SP_WHO: SPID= n, ECID= 0,1,2,3,4), and at this point the procedure failed with error 1205 (Dead Lock victim).
Thank you for your help.
Aldair
Monday, March 26, 2012
Query with a left outer join is not working as expected
The query below is suppose to join customer information with sales and vendo
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
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
Subscribe to:
Posts (Atom)