Using CTEs to simplyfy SQL

Common Table Expressions, CTE for short, is a powerful tool that I use in views, debugging, testing and simplyfying SQL.

CTE Structure

With CTE (EMPLID, DEPARTMENT) AS ( SELECT EMPLID, DEPTID FROM PS_CURRENT_JOB WHERE JOBCODE = 'SUPER')
SELECT * FROM CTE


The CTE above would return everyone from the current job record where the JOBCODE is 'SUPER'.  This not very useful example, but it does show the syntax.

So how can CTE's simply sql?  For instance, if you were writing a process that needs to derive an value to be used in the selection and in the criteria, if you did not use a CTE then your SQL could look like this below:

select 
A.Emplid,
A.benfit_plan,
M.MAX_ANNUAL_CONTRIB,
A.ANNUAL_PLEDGE
CASE A.CNTRL_BENEFIT_PLAN WHEN ' ' THEN 0 ELSE CASE A.ZZ_HSA_YEARS WHEN 1 THEN DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB1 , I.Zz_Hsa_Fam1)/2 ELSE DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB2 , I.Zz_Hsa_Fam2/2) END END as UMB_CONTRIB
FROM PS_ZZ_UMB_TBL A., PS_ZZ_HSA_LIMITS M, PS_ZZ_INSTALLATION I
WHERE A.EMPLID = M.EMPLID AND A.EMPL_RCD = M.EMPL_RCD
 AND M.MAX_ANNUAL_CONTRIB < ( CASE A.CNTRL_BENEFIT_PLAN WHEN ' ' THEN 0 ELSE CASE A.ZZ_HSA_YEARS WHEN 1 THEN DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB1 , I.Zz_Hsa_Fam1)/2 ELSE DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB2 , I.Zz_Hsa_Fam2/2) END END + A.ANNUAL_PLEDGE)


The problem with this sql is that you have two copies of the same case statement.   Here is how you can use a CTE to only manage one case statement.



With CTE (EMPLID, BENEFIT_PLAN, MAX_ANNUAL_CONTRIB, ANNUAL_PLEDGE UMB_CONTRIB) AS ( select 
A.Emplid,
A.benfit_plan,
M.MAX_ANNUAL_CONTRIB,
A.ANNUAL_PLEDGE
CASE A.CNTRL_BENEFIT_PLAN WHEN ' ' THEN 0 ELSE CASE A.ZZ_HSA_YEARS WHEN 1 THEN DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB1 , I.Zz_Hsa_Fam1)/2 ELSE DECODE(L.COVRG_CD ,'1' , I.ZZ_HSA_CONTRIB2 , I.Zz_Hsa_Fam2/2) END END as UMB_CONTRIB
FROM PS_ZZ_UMB_TBL A., PS_ZZ_HSA_LIMITS M, PS_ZZ_INSTALLATION I
WHERE A.EMPLID = M.EMPLID AND A.EMPL_RCD = M.EMPL_RCD)
SELECT EMPLID, BENEFIT_PLAN, UMB_CONTRIB FROM CTE WHERE  MAX_ANNUAL_CONTRIB < ( ANNUAL_PLEDGE + UMB_CONTRIB)