January 16, 2007 at 7:54 am
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,
January 16, 2007 at 3:36 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply