query optimization

[mike9]

If distinct values is not a issue in your case i recommand to use UNION ALL instead of UNION.

The use of an OR instead of executing twice the query may also imporve the preformance:

(A.nm=B.nm AND

  (  (A.dt=B.dt AND A.cd=B.cd AND B.lnnbr = 0 )

  OR  (A.lnnbr=B.lnnbr AND B.lnnbr >0)  

  )

)