Error executing Query having MERGE Join

Question

Hi,

I m Executing a Query as Below:

               

SELECT BT.BranchId, BT.Itemcode, BT.Qty,BT.OpenRate P_Rate          
FROM BranchStock BT INNER Merge JOIN
Price_Master P ON BT.ItemCode = P.Itemcode                
WHERE BT.Status = 'S' AND BT.CompId = 'C001' AND BT.Qty > 0               
and (BT.TranDate between 26290 and 26295)

It's working fine. But when I'm adding a Condition in the Where clause as

SELECT BT.BranchId, BT.Itemcode, BT.Qty,BT.OpenRate P_Rate          
FROM BranchStock BT INNER Merge JOIN                
Price_Master P ON BT.ItemCode = P.Itemcode                
WHERE BT.Status = 'S' AND BT.CompId = 'C001' AND BT.Qty > 0               
and (BT.TranDate between 26290 and 26295) and BT.Itemcode = '2NW035044'

I'm getting an error as "Msg 8622, Level 16, State 1, Line 1 

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."

Though The given Itemcode exist in the Record.

Pls Help me in this regard.

Thank u in Advance.

Regards,

Sanjaya

Answer 1

Is it error coming during the execution of above query or for a SP in which this query is part?

---

Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 2

SELECT BT.BranchId, BT.Itemcode, BT.Qty,BT.OpenRate P_Rate          
FROM BranchStock BT INNER Merge JOIN                
Price_Master P ON BT.ItemCode = P.Itemcode  
and BT.Itemcode = '2NW035044'              
WHERE BT.Status = 'S' AND BT.CompId = 'C001' AND BT.Qty > 0               
and (BT.TranDate between 26290 and 26295) 

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

Answer 3

In Both Cases its coming.

When I'm directly executing the query or through SP.

Answer 4

Thanks 4 ur reply.

But getting the same error

Answer 5

Check this post

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/027a18c4-68ae-4418-8a56-1f574e19a275

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

Answer 6

--i am getting the result for this
select a.maker,a.model,a.type from Product a
inner merge join PC c 
on a.model = c.model
where a.model = 1121

--and not getting for this
select a.maker,a.model,a.type from Product a
inner merge join PC c 
on a.model = c.model
and a.model = '1121'

Also check craig's blog,

http://blogs.msdn.com/b/craigfr/archive/2009/04/28/implied-predicates-and-query-hints.aspx

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

Answer 7

So remote the join hint from the query. That is, MERGE is INNER MERGE JOIN is a hint that instructs SQL Server to implement the join in question with Merge Join operator.

Join hints is something you should use if you have a very good understanding of optimization and query plans.

---

Erland Sommarskog, SQL Server MVP, [e-mail]