title: How do I correctly filter on numeric columns with custom format in Oracle APEX?
body: <p>In our APEX reports, we use custom number formats to round numeric values to a given (user-defined precision). E.g. given this data:</p>
  
  <pre><code>create table round_test(pk number not null primary key, value number);
  insert into round_test(pk, value) values (1, 0.11);
  insert into round_test(pk, value) values (2, 0.19);
  insert into round_test(pk, value) values (3, 0.20);
  insert into round_test(pk, value) values (4, 0.21);
  insert into round_test(pk, value) values (5, 0.23);
  insert into round_test(pk, value) values (7, 0.28);
  </code></pre>
  
  <p>I've created an interactive report to display the <code>VALUE</code> column with format <code>999D9</code> (for illustration purposes, I've added an <code>ORIGINAL_VALUE</code> column that displays the data without a number format, i.e. with full precision - see screenshot below).</p>
  
  <p>Now if I click the column header to filter by this column, I get duplicate values (e.g. 0.2 appears four times - once for 0.19, 0.20, 0.23 and 0.28):</p>
  
  <p><img src="https://i.stack.imgur.com/qO1Cz.png" alt="Filter drop down"></p>
  
  <p>That's bad enough, but if I click one of these values, APEX filters by the <em>exact value</em> instead of the rounded one:</p>
  
  <p><img src="https://i.stack.imgur.com/26WfO.png" alt="Filter result"></p>
  
  <p>How can I </p>
  
  <ul>
  <li>get rid of the duplicates in the drop-down</li>
  <li>force APEX to filter by the rounded value</li>
  </ul>
  
  <p><strong>Note:</strong> Creating a view with the rounded values and using that in the report definition is not a viable approach, since our reports include a custom export function that allows the users to export the data with full precision.</p>
  
  <p><strong>UPDATE</strong>
  The SQL query for the report is quite simple:</p>
  
  <pre><code>SELECT 
    pk,
    value,
    value AS original_value
  FROM round_test 
  </code></pre>
  
  <p>The behaviour is the same in APEX 4.2 (which we currently use) and APEX 5.0 (which I used at <a href="http://apex.oracle.com" rel="nofollow noreferrer">http://apex.oracle.com</a> to create the example).</p>