title: SQL - What is the performance impact of having multiple CASE statements in SELECT - Teradata
body: <p>So I have a query that requires a bunch of CASE statements in the SELECT. This was not the orginal design but part of a compromise.</p>
  
  <p>So the query looks something like this:</p>
  
  <pre><code>SELECT
    CONT.TABLE.FINC_ACCT_NM,
    CONT.TABLE.FINC_ACCT_ID,
    CONT.TABLE.CURR_END_OF_PERD_ACTL_VAL,
    CONT.TABLE.PREV_END_OF_PERD_ACTL_VAL,
    CONT.TABLE.VARNC_PLAN_VAL,
    CONT.TABLE.OUTLOOK_BDGT_PLAN_VAL,
    CONT.TABLE.PERD_END_RPT_DT,
    CONT.TABLE.PLAN_VERS_NM,
    CONT.TABLE.FRMT_ACTL_CD,
    CONT.TABLE.FRMT_PLAN_CD,
    CONT.TABLE.RPT_PERD_TYPE_CD,
    CASE 
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Net Interest Income'  
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Non Interest Income'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Non-Interest Expense'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Total Marketing Expense'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Total Operating Expense'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Pre-Provision Earnings (before tax)'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Net Charge-offs'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Other'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      '  Allowance Build (Release)'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Provision Expense'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Pretax Income'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Tax Expense'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'NIAT'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'EPS'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Ending Loans - HFI'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'avg'       then      'Average Loans - HFI'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'avg'       then      'Average Earning Assets'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Ending Deposits'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'avg'       then      'Average Deposits'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'NIM on Loans'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Revenue Margin'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'AC579'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Charge off rate'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Efficiency ratio'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'ROA'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'ROE'
                  WHEN ( CONT.TABLE.FINC_ACCT_ID )=           'XXXX'        and ( CONT.TABLE.BAL_TYPE_CD ) =             'EOP'      then      'Return on Allocated Capital (ROAC)'
  
  
  
    ELSE ( CONT.TABLE.FINC_ACCT_NM ) end
  FROM
    CONT.TABLE
  WHERE
    (
     (
      ( ( CONT.TABLE.PERD_END_RPT_DT ) = (
  
  SELECT Max(Perd_END_RPT_DT) 
  FROM CONT.TABLE
  Where VERS_NM='Actual'
     AND RPT_PERD_TYPE_CD = 'Q'
     AND DATA_VLDTN_IND='Y'
  )
     AND RPT_PERD_TYPE_CD = 'Q'
    AND DATA_VLDTN_IND='Y'  )
      OR
      ( ( CONT.TABLE.PERD_END_RPT_DT ) = (
  
  SELECT Max(Perd_END_RPT_DT) 
  FROM CONT.TABLE
  Where VERS_NM='Actual'
     AND RPT_PERD_TYPE_CD = 'M'
     AND DATA_VLDTN_IND='Y'
  ) 
  
    AND RPT_PERD_TYPE_CD = 'M'
    AND DATA_VLDTN_IND='Y'  )
     )
     AND
     ( ( CONT.TABLE.DATA_VLDTN_IND )='Y'  )
     AND
     ( ( CONT.TABLE.FINC_ACCT_ID )IN ('AC0006470','AC8000199','AC8002145','AC0006586','AC8000094')  AND ( CONT.TABLE.DEPT_ID )='OR80637'  )
    )
  </code></pre>
  
  <p>My question is what affect would changing all those CASE statements to direct column references have on performance.</p>
  
  <p>In other words: If I changed every CASE statement to just a column name and removed all CASE statements from the query would there be a large impact on performance and why?</p>
  
  <p>I am testing this out so I can figure out if performance is affected but I am just as interested in the details of WHY? (Technical details of why)</p>
  
  <p>Thanks for your help!</p>