Left outer join in where clause

• byron.peterson

  Grasshopper

  Points: 13

  More actions

  December 11, 2007 at 5:32 am

  #377450

  Hi all,

  I have been tasked to re-write some sql2000 stored procs in preparation for moving to sql 2005. The SP in question has a conditional left outer join in the where clause that is troubling me. Just wondering if anyone can see a way to rewrite it:

  Select

  ...

  FROM TableA A (NOLOCK),

  TableC C(NOLOCK),

  TableT AS T (NOLOCK),

  TableV V (NOLOCK),

  TableU U (NOLOCK)

  WHERE V.Tran_ID = P.Tran_ID AND

  (CASE

  WHEN T.CodeA IN('01', '02','03', Then

  ISNULL(C.ResultA, C.ResultB)

  WHEN T.CodeB = 'B' Then

  ISNULL(C.ResultC, C.ResultB)

  ELSE

  C.ResultB

  END) *= U.ResultB

  ....

  Any ideas?

  Thanks for your help,

  B

• Ken McKelvey

  SSCoach

  Points: 18939

  More actions

  December 11, 2007 at 7:35 am

  #758957

  Without the rest of your tables (P as in P.Tran_ID?) and the rest of the WHERE clause, your post does not make sense.