tough query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

tough query

Author

Topic

DavidD
Yak Posting Veteran

73 Posts

Posted - 2005-09-19 : 20:42:01

Dear all

I am trying to create a piece of dynamic sql that allows me to pass in a group of products and a group of detailorders and then returns the callIDs that satisy those criteria only.
I do not want callids that do not satisy all requirements.
eg
@product = '(1,32)'
@detailorder = '(1,2)'

When passed into the below data should return only calls 104 and 105, so simple in statements do not work, I want only calls where products 1 and 32 were detailed in positions 1 and 2(which is which is unimportant).
The solution should be flexible enough to handle up to 5 products and detailorders, I feel there should be some way of doing it with a correlated sub-query, but the only solution I have found is a very ugly udf that kills performance (this is a simplified component of a much larger query).
The dummy data is below, if anyone can come up with an ingeneous solution it would be very much appreciated

Regards
David

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CallTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CallTemp]
GO
CREATE TABLE [dbo].[CallTemp] (
[CallID] [int] NULL ,
[ProductID] [int] NULL ,
[DetailOrder] [tinyint] NULL
) ON [PRIMARY]
GO
insert into CallTemp (CallID, ProductID, DetailOrder)
values (101,1,1)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (101,2,2)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (101,3,3)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (102,32,1)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (103,3,1)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (104,32,1)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (104,1,2)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (105,1,1)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (105,32,2)
go
insert into CallTemp (CallID, ProductID, DetailOrder)
values (105,3,3)
go

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 22:57:58

Can you pass the @product and @detailorder "lists" as a parameter to an SProc and SPLIT them and then JOIN the split data to the main CallTemp table?

Or was that your very ugly udf that kills performance?

Kristen

DavidD
Yak Posting Veteran

73 Posts

Posted - 2005-09-20 : 01:29:44

Hi Kristen,

Thanks for the reply.

Basically yes, the only solution I have found is to seperate out productids and then build a complex series of subqueries in a udf - something like this (simplified version)

select distinct t1.callid
from
(select callid
from CallTemp
where productid = @product1 and CallDetail in @CallDetail) t1
join
(select callid
from calltemp
where productid = @product2 and CallDetail in @CallDetail) t2
on t1.callid = t2.callid

As it has to be dynamic and handle 5 products it gets very complex and makes the query grind to a halt

Regards
David

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 01:39:05

Hmmm ... I was thinking something along the lines of:


DECLARE	@product varchar(1000),
	@detailorder varchar(1000)
SELECT	@product = '1,32',
	@detailorder = '1,2'

SELECT	CT.*
FROM	CallTemp AS CT
	JOIN dbo.my_FN_Split(@product) AS P
		ON P.value = ProductID
	JOIN dbo.my_FN_Split(@detailorder) AS O
		ON O.value = DetailOrder

which gives


CallID      ProductID   DetailOrder 
----------- ----------- ----------- 
101         1           1
102         32          1
104         32          1
104         1           2
105         1           1
105         32          2

(6 row(s) affected)

Dunno how close that is to the results you want?

To just get the ones with sufficient matches adding

GROUP BY CallID
HAVING COUNT(*) = 2

would do the trick (the "2" would need to be the COUNT(*) of the number of values that the SPLIT function detects)

Kristen

DavidD
Yak Posting Veteran

73 Posts

Posted - 2005-09-20 : 02:55:50

Hi Kristen,

Good idea, it could be a solution as you can even remove the functions by using

select * from calltemp
where productid in (1,32)
and detailorder in (1,2)

to get the same result set

the count(*) would be the number of productids passed into the query - 2 in this case, I am just trying to work out a way to get this out of the string without using a while loop, if this can be done without too much cost I could be in business.

Thanks for your help
David

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 07:26:07

"as you can even remove the functions by using"

Yeah, but that would require dynamic SQL ....

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources