March 23, 2009 at 12:12 pm
I created a table that has napathirdparty PN and just put distinct PN's in that one. This substntially fixed the long running query problem that this was doing.
SELECT DISTINCT MIN(Application.ApplicationID) AS Expr1, ApplicationProduct.ProductControlID
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
Product ON ApplicationProduct.ProductControlID = Product.ProductControlID INNER JOIN
NapaThirdPartyPN ON Product.PartNumber = NapaThirdPartyPN.PN
WHERE (Product.ProductLineID = 10290)
GROUP BY ApplicationProduct.ProductControlID
March 23, 2009 at 12:19 pm
Hey FoxJazz,
I couldnt really read through all the posts..but from what I have understood , ur requirment seems to be that from the set of records having the same application id with respect to a particular product control Id , u would want to delete all but one.....
anyways for this what I suggest is go ahead with the use of the cursor , identify the set of duplicates for the respect product control id and do whats shown below.....
say your cursor would look something like this.......
declare cur cursor fast_forward
FOR
select ProductControlId ,Count(ApplicationId)
from sometable
group by ProductControlId
Having count(ApplicationId) > 1
open cur
fetch next into @ProductId, @Count
while fetch_status = 0
begin
set rowcount @Count - 1
--delete statement here with all your requirements
set rowcount 0
--the set rowcount property would affect only count - 1 records...
fetch next into @ProductId, @Count
end
I m not sure if this is what you were looking for .....anyways
Cheers
Daniel
March 23, 2009 at 12:22 pm
Linson.Daniel (3/23/2009)
Hey FoxJazz,I couldnt really read through all the posts..but from what I have understood , ur requirment seems to be that from the set of records having the same application id with respect to a particular product control Id , u would want to delete all but one.....
anyways for this what I suggest is go ahead with the use of the cursor , identify the set of duplicates for the respect product control id and do whats shown below.....
say your cursor would look something like this.......
declare cur cursor fast_forward
FOR
select ProductControlId ,Count(ApplicationId)
from sometable
group by ProductControlId
Having count(ApplicationId) > 1
open cur
fetch next into @ProductId, @Count
while fetch_status = 0
begin
set rowcount @Count - 1
--delete statement here with all your requirements
set rowcount 0
--the set rowcount property would affect only count - 1 records...
fetch next into @ProductId, @Count
end
I m not sure if this is what you were looking for .....anyways
Cheers
Daniel
Linson.Daniel -- do you know what can of worms you are opening up here?
March 23, 2009 at 12:25 pm
Linson.Daniel (3/23/2009)
Hey FoxJazz,I couldnt really read through all the posts..but from what I have understood , ur requirment seems to be that from the set of records having the same application id with respect to a particular product control Id , u would want to delete all but one.....
anyways for this what I suggest is go ahead with the use of the cursor , identify the set of duplicates for the respect product control id and do whats shown below.....
say your cursor would look something like this.......
declare cur cursor fast_forward
FOR
select ProductControlId ,Count(ApplicationId)
from sometable
group by ProductControlId
Having count(ApplicationId) > 1
open cur
fetch next into @ProductId, @Count
while fetch_status = 0
begin
set rowcount @Count - 1
--delete statement here with all your requirements
set rowcount 0
--the set rowcount property would affect only count - 1 records...
fetch next into @ProductId, @Count
end
I m not sure if this is what you were looking for .....anyways
Cheers
Daniel
This can be done without using a cursor.
March 23, 2009 at 12:25 pm
error on this query:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
is there an easy way to solve this error?
SELECT DISTINCT Application.ApplicationID, NapaThirdParty.PN, Product.PartNumber, CategoryAAIAmap.GenCategoryID
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
NapaThirdParty INNER JOIN
Product ON NapaThirdParty.PN = Product.PartNumber INNER JOIN
CategoryAAIAmap ON NapaThirdParty.[AAIA ID] = CategoryAAIAmap.[AAIA ID] ON ApplicationProduct.ProductControlID = Product.ProductControlID
WHERE (Product.ProductLineID = 10290) and applicationproduct.applicationid not in (
SELECT DISTINCT MIN(Application.ApplicationID) AS Expr1, ApplicationProduct.ProductControlID
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
Product ON ApplicationProduct.ProductControlID = Product.ProductControlID INNER JOIN
NapaThirdPartyPN ON Product.PartNumber = NapaThirdPartyPN.PN
WHERE (Product.ProductLineID = 10290)
GROUP BY ApplicationProduct.ProductControlID)
March 23, 2009 at 12:28 pm
foxjazz, I am more than willing to help here, but I am not writing a line of code without the sample data and expected results. I need something to test with (sample data) and against (expected results based on the sample data).
The sample data should be in the form of insert statements (we need tobe able to cut, pase, and execute). The expected results can just be a formatted table to compare results against.
March 23, 2009 at 12:29 pm
by George, I think I got it:
SELECT DISTINCT Application.ApplicationID
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
NapaThirdParty INNER JOIN
Product ON NapaThirdParty.PN = Product.PartNumber INNER JOIN
CategoryAAIAmap ON NapaThirdParty.[AAIA ID] = CategoryAAIAmap.[AAIA ID] ON ApplicationProduct.ProductControlID = Product.ProductControlID
WHERE (Product.ProductLineID = 10290) and applicationproduct.applicationid not in (
SELECT DISTINCT MIN(Application.ApplicationID) AS Expr1
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
Product ON ApplicationProduct.ProductControlID = Product.ProductControlID INNER JOIN
NapaThirdPartyPN ON Product.PartNumber = NapaThirdPartyPN.PN
WHERE (Product.ProductLineID = 10290)
GROUP BY ApplicationProduct.ProductControlID)
so:
delete application,applicationproduct
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
NapaThirdParty INNER JOIN
Product ON NapaThirdParty.PN = Product.PartNumber INNER JOIN
CategoryAAIAmap ON NapaThirdParty.[AAIA ID] = CategoryAAIAmap.[AAIA ID] ON ApplicationProduct.ProductControlID = Product.ProductControlID
WHERE (Product.ProductLineID = 10290) and applicationproduct.applicationid not in (
SELECT DISTINCT MIN(Application.ApplicationID) AS Expr1
FROM ApplicationProduct WITH (nolock) INNER JOIN
Application ON ApplicationProduct.ApplicationID = Application.ApplicationID INNER JOIN
Product ON ApplicationProduct.ProductControlID = Product.ProductControlID INNER JOIN
NapaThirdPartyPN ON Product.PartNumber = NapaThirdPartyPN.PN
WHERE (Product.ProductLineID = 10290)
GROUP BY ApplicationProduct.ProductControlID)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply