Help please

• shwetha

  Ten Centuries

  Points: 1248

  More actions

  January 16, 2007 at 7:54 am

  #175525

  Select TOP 100 PERCENT bp_group_name

  FROM mf_01createbp_grp

  WHERE bp_group_name NOT IN

  SELECT TOP 100 PERCENT

  Eploltp01.sch_bpref.businesspartnergroup.bp_group_name

  FROM sch_bpref.businesspartnergroup

  ORDER BY bp_group_name

  I get the below error when executing the above script, I'm pulling the data from tables residing in two different databases on the same server

  Server: Msg 4104, Level 16, State 1, Line 1

  The multi-part identifier "Eploltp01.sch_bpref.businesspartnergroup.bp_group_name" could not be bound.

  help greatly appreciated -

  Regards,

• R Michael

  SSCarpal Tunnel

  Points: 4211

  More actions

  January 16, 2007 at 3:36 pm

  #683632

  Try This:

  Select TOP 100 PERCENT bp_group_name

  FROM mf_01createbp_grp

  WHERE bp_group_name NOT IN

  (SELECT TOP 100 PERCENT

  BPG.bp_group_name

  FROM Eploltp01.sch_bpref.businesspartnergroup

  ORDER BY bp_group_name)

  Are you doing the order by in the subquery to create the conditions for a merge semi-join? According to the Query Optimizer team blog, SQL 2005 doesn't guarentee the order of the results in your subquery when you use TOP 100 PERCENT thus the optimizer might not choose a MERGE JOIN. Adam Mechanic pointed out in his blog that you can use TOP 2147483647 and that will guarentee order in your subquery.

  SQL guy and Houston Magician