title: ORACLE - Select Count on a Subquery
body: <p>I've got an Oracle table that holds a set of ranges (RangeA and RangeB). These columns are varchar as they can hold both numeric and alphanumeric values, like the following example:</p>
  
  <pre><code>ID|RangeA|RangeB
  1 |   10 |   20
  2 |   21 |   30
  3 | AB50 | AB70
  4 | AB80 | AB90
  </code></pre>
  
  <p>I need to to do a query that returns only the records that have numeric values, and perform a Count on that query. So far I've tried doing this with two different queries without any luck:</p>
  
  <p>Query 1:<br/></p>
  
  <pre><code>SELECT COUNT(*) FROM (
  SELECT RangeA, RangeB FROM table R
  WHERE upper(R.RangeA) = lower(R.RangeA)
  ) A
  WHERE TO_NUMBER(A.RangeA) &lt;= 10
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>WITH A(RangeA,RangeB) AS(
  SELECT RangeA, RangeB FROM table 
  WHERE upper(RangeA) = lower(RangeA)
  )
  SELECT COUNT(*) FROM A WHERE TO_NUMBER(A.RangeA) &lt;= 10
  </code></pre>
  
  <p>The subquery is working fine as I'm getting the two records that have only numeric values, but the COUNT part of the query is failing. I should be getting only 1 on the count, but instead I'm getting the following error:</p>
  
  <pre><code>ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  </code></pre>
  
  <p>What am I doing wrong? Any help is much appreciated.</p>