June 30, 2014 at 4:20 am
Can we rewrite the outer query mention below particularly NOT IN statement of the outer query
SELECT DISTINCT
A.sno ,
pm.ProductID ,
A.ProductName ,
2
FROM
(
SELECT
mer.sno ,
SUBSTRING(mer.MATERIAL , 1 , CHARINDEX('.' , mer.MATERIAL) - 1)AS ProductName
FROM
MOB.EOR mer WITH (NOLOCK)
LEFT OUTER JOIN TLA.Tanks tks WITH (NOLOCK) ON mer.sno = tks.TS
WHERE tks.TS IS NULL
AND MER.sno IS NOT NULL
AND CHEMICAL = 1
--AND MER.SoldTo = @SoldTo
--AND MER.SHIPTO = @ShipTo
)AS A
LEFT OUTER JOIN TLA.ProductsMaster pm WITH (NOLOCK) ON pm.ProductName = A.ProductName
WHERE a.ProductName NOT IN(
SELECT
pm.ProductName
FROM
TLA.ProductsMaster pm WITH (NOLOCK)
INNER JOIN TLA.Tanks t WITH (NOLOCK) ON t.ProductId = pm.ProductID);
June 30, 2014 at 4:37 am
What's the problem? Performance? Try replacing the NOT IN with NOT EXISTS.
edit: any special reason the query is full with "WITH (NOLOCK)"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 30, 2014 at 4:39 am
the objective is to replace the NOT in with some joins
June 30, 2014 at 4:43 am
ramrajan (6/30/2014)
the objective is to replace the NOT in with some joins
OK, so why don't you?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 30, 2014 at 4:45 am
i am new to this so need help
June 30, 2014 at 4:56 am
Replace the NOT IN with a LEFT OUTER JOIN and filter on NULL values.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 30, 2014 at 8:37 am
ramrajan (6/30/2014)
the objective is to replace the NOT in with some joins
What are you expecting to get from this? It might affect performance and the result should be the same (unless you have NULL values on productname column on ProductMaster table).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply