Hi Ann,

>>The problem is with the query, not with the plan. In FROM part of the

>>select, you're asking for all records from the NL_Transactions table,
>>with optional matching records from the JC_Allocations table and
>>optional matching records from the JC_Jobs table. The WHERE clause
>>subsequently eliminates NL_Transaction records that don't have a
>>matching JC_Allocations record, but by then the damage is done.

>>Change the first left outer join to an inner join.

>>As a general rule, referencing a table that's the right side of a left

>>outer join in the WHERE clause is a red flag. Either the condition
>>should be in the ON clause or the join should be inner, not outer.

I was afraid of that. The problem is that depending on the value of the
input parameter, I sometimes want the effects of an inner join, and
other times want a left outer join. I was hoping to avoid having to
maintain 2 separate stored procedures that only differ in the way that
they are joined. Pity you can't do a selective join ;-)

Thank you for your help.

Maya