Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Error executing Query having MERGE Join

Answered Error executing Query having MERGE Join

  • Friday, March 08, 2013 12:33 PM
     
     
    Sign In to Vote
    0

    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


     

All Replies

  • Friday, March 08, 2013 1:02 PM
     
     
    Sign In to Vote
    0
    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.

     
  • Friday, March 08, 2013 1:03 PM
     
      Has Code
    Sign In to Vote
    0
     
    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

     
  • Friday, March 08, 2013 1:06 PM
     
     
    Sign In to Vote
    0

    In Both Cases its coming.

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

     
  • Friday, March 08, 2013 1:06 PM
     
     
    Sign In to Vote
    0

    Thanks 4 ur reply.

    But getting the same error

     
  • Friday, March 08, 2013 1:13 PM
     
     
    Sign In to Vote
    0

    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

     
  • Friday, March 08, 2013 1:25 PM
     
      Has Code
    Sign In to Vote
    0
     
    --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

     
  • Friday, March 08, 2013 1:56 PM
     
     Answered
    Sign In to Vote
    0

    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, esquel@sommarskog.se