Tuning SQL via case when statement

原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

changed to

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
上一篇:python获取当前文件路径以及父文件路径


下一篇:javaScript基础-02 javascript表达式和运算符