title: ORACLE - DECODE view predicate and apply on base table
body: <p>I have a view defined as below</p>
  <pre><code>CREATE VIEW DQ_DB.DQM_RESULT_VIEW
  AS SELECT
      res.ACTIVE_FL AS ACTIVE_FL,
      res.VERSION as VERSION,
      res.rule_constituents_tx,
      nvl(ruletable.rule_desc,'N/A') AS rule_ds,
      nvl(res.effective_dt, TO_DATE('31-dec-9999','dd-mon-yyyy')) AS effective_dt,
      nvl(res.rule_id,'N/A') AS rule_id,
      res.audit_update_ts AS rule_processed_at,
      res.load_dt,
      res.vendor_group_key,
      nvl(res.vendor_entity_key,'N/A') AS vendor_entity_key,
      res.vendor_entity_producer_nm,
      (SELECT category_value_tx FROM dq_db.category_lookup_view WHERE category_nm = 'RESULT_STATUS_NB' AND category_value_cd = res.result_status_nb ) AS result,
      --catlkp.category_value_tx as result,
      res.entity_type,
      nvl(rgrp.grp_nm,'N/A') AS rule_category,
      nvl(ruletable.rule_nm,'N/A') AS rule_nm,
      feedsumm.feed_run_nm AS file_nm,
      res.application_id AS application,
      res.data_source_id AS datasource,
      res.entity_nm,
      res.rule_entity_effective_dt,
      res.result_id,
      dim.dimension_nm,
      dim.sub_dimension_nm,
      ruletable.execution_env AS execution_env,
      ruletable.ops_action AS ops_action,
      rulefunctiontable.func_nm AS rule_func_nm,
  --        nvl2(res.primary_dco_sid,dq_db.get_dco_name(res.primary_dco_sid),null) AS dco_primary,
  --        nvl2(res.delegate_dco_sid,dq_db.get_dco_name(res.delegate_dco_sid),null) AS dco_delegate,
      res.primary_dco_sid AS dco_primary,
      res.delegate_dco_sid AS dco_delegate,
      ruletable.data_concept_id AS data_concept_id,
      res.latest_result_fl as latest_result_fl,
      res.batch_execution_ts as batch_execution_ts
  FROM
      dq_db.dqm_result res
          --LEFT OUTER JOIN dq_db.category_lookup_view catlkp on (catlkp.category_nm = 'RESULT_STATUS_NB' AND catlkp.category_value_cd = res.result_status_nb)
          LEFT OUTER JOIN dq_db.feed_run_summary feedsumm ON res.vendor_group_key = feedsumm.batch_id
          LEFT OUTER JOIN dq_db.dqm_rule ruletable ON res.rule_id = ruletable.rule_id
          LEFT OUTER JOIN dq_db.dqm_rule_grp rgrp ON ruletable.rule_grp_id = rgrp.rule_grp_id
          LEFT OUTER JOIN dq_db.dqm_rule_function rulefunctiontable ON ruletable.func_id = rulefunctiontable.func_id
          LEFT OUTER JOIN dq_db.dq_dimension_view dim ON dim.dimension_id = ruletable.dimension_id
  </code></pre>
  <p><code>result</code> column of view is a lookup value generated from subquery condition which translates code as
  below.</p>
  <p><code> 0|PASS 1|ALERT 2|ERROR</code></p>
  <p>My web application adds few predicates to this view which are pushed to base tables. But one particular predicate on view shown below is not pushed due to inline nature of the predicate.</p>
  <p><code>select * from dqm_result_view where result IN ('ALERT','ERROR')</code></p>
  <p>Right now this query applies filter after view JOINs are executed as there is no way to push predicate to DQM_RESULT table.</p>
  <p>What is need is.. if we get that result predicate then apply code <code>0,1,2</code> instead of applying <code>result</code> predicate at end so that data is filtered ahead of time for JOINs from <code>DQM_RESULT</code> base table and thus improve performance. Any idea on how to achieve this?</p>