原SQL如下：SQL的主要问题是红色部分居然通过标量查询，反复的查找与SQL相同的基表，很显然这个可以用case when来简化。

select 
a.TRAN_ID,
a.AMOUNT,
a.BALANCE,
a.INVAMT,
a.PROMISED,
a.INVNO,
a.RCLNUM,
b.PROBLEM_ID,

a.TRANTYPE,
a.TYPE,
a.DUEDATE,
a.INVDATE,
a.RCLDATE,
a.LASTTYPE,
a.LOCBAL,
a.CUSTNO,
b.STATUS,
a.PAYMENTS_PENDING,


isnull((  SELECT 'Y'  FROM GPCOMP1.GPRECL aa  with (NOLOCK) LEFT OUTER JOIN GPCOMP1.GPPROB b  with (NOLOCK)

 ON (aa.tran_id = b.open_invoice_tran_id), GPCOMP1.GPTRCTRL c  with (NOLOCK)        WHERE  aa.tran_id = a.tran_id       

 AND aa.trantype = c.trantype           AND           (            (               c.CAPPLSTRAT = 'Y'            

 AND (b.PROBLEM_ID IS NULL OR (b.PROBLEM_ID IS NOT NULL AND b.STATUS = 'C' ))  

 AND ((c.DAPPLSTRAT = 'Y' AND c.CAPPLSTRAT = 'Y') OR C.SAPPLYSTRAT='Y' OR (C.SAPPLYSTRAT='N' AND    

 aa.INVNO not in (select distinct (invno) from GPCOMP1.GPRECL r  with (NOLOCK)       

 where tran_id = (select open_invoice_tran_id from GPCOMP1.GPPROB p  with (NOLOCK)  where p.open_invoice_tran_id = r.tran_id))))            )            OR            ( c.DAPPLSTRAT = 'Y' AND (b.PROBLEM_ID IS NOT NULL AND b.STATUS <> 'C' ) )            )            AND          (             ( c.PPAPPLSTRAT = 'Y' AND aa.PAYMENTS_PENDING = 'Y' )             OR             ( c.NPPAPPLSTRAT = 'Y' AND aa.PAYMENTS_PENDING != 'Y' )          )       ),'N') 
as STRATEGIC  

from GPCOMP1.GPRECL a  with (NOLOCK) LEFT OUTER JOIN GPCOMP1.GPPROB b  with (NOLOCK)  on (b.OPEN_INVOICE_TRAN_ID = a.TRAN_ID)

where a.CUSTNO= @P0  order by a.INVNO

select

a.TRAN_ID,

a.AMOUNT,

a.BALANCE,

a.INVAMT,

a.PROMISED,

a.INVNO,

a.RCLNUM,

b.PROBLEM_ID,

a.TRANTYPE,

a.TYPE,

a.DUEDATE,

a.INVDATE,

a.RCLDATE,

a.LASTTYPE,

a.LOCBAL,

a.CUSTNO,

b.STATUS,

a.PAYMENTS_PENDING,

CASE

WHEN c.PPAPPLSTRAT = 'Y'

     AND a.PAYMENTS_PENDING = 'Y'

     AND c.DAPPLSTRAT = 'Y'

     AND (b.PROBLEM_ID IS NOT NULL AND b.STATUS <> 'C' )

THEN 'Y'

WHEN c.PPAPPLSTRAT = 'Y'

     AND a.PAYMENTS_PENDING = 'Y'

     AND c.CAPPLSTRAT = 'Y'

     AND (b.PROBLEM_ID IS NULL OR (b.PROBLEM_ID IS NOT NULL AND b.STATUS = 'C' ))

     AND (c.DAPPLSTRAT = 'Y' or C.SAPPLYSTRAT='Y' or (C.SAPPLYSTRAT='N' AND b.open_invoice_tran_id is null))

THEN 'Y'

WHEN c.NPPAPPLSTRAT = 'Y'

     AND a.PAYMENTS_PENDING != 'Y'

     AND c.DAPPLSTRAT = 'Y'

     AND (b.PROBLEM_ID IS NOT NULL AND b.STATUS <> 'C' )

THEN 'Y'

WHEN c.NPPAPPLSTRAT = 'Y'

     AND a.PAYMENTS_PENDING != 'Y'

     AND c.CAPPLSTRAT = 'Y'

     AND (b.PROBLEM_ID IS NULL OR (b.PROBLEM_ID IS NOT NULL AND b.STATUS = 'C' ))

     AND (c.DAPPLSTRAT = 'Y' or C.SAPPLYSTRAT='Y' or (C.SAPPLYSTRAT='N' and b.open_invoice_tran_id is null))

THEN 'Y'

ELSE 'N'

END  as  STRATEGIC

FROM GPCOMP1.GPRECL a

LEFT OUTER JOIN GPCOMP1.GPPROB b      on (b.OPEN_INVOICE_TRAN_ID = a.TRAN_ID)

LEFT OUTER JOIN GPCOMP1.GPTRCTRL c    on (c.trantype = a.trantype)

where a.CUSTNO= ''  order by a.INVNO