title: Invalid number in Oracle SQL Case
body: <p>Hi im habing trouble with a SQL case, the problem is im trying to run a case with 7 different columns, the columns can have different kinds of data (string,date,number) depending on an id.</p>
  
  <p>This means that under some id's the rows in a column will be string while under other ids the rows in a column will be number. </p>
  
  <p>I realise this isn't a conventional use of astructured database, but this specific table serve a specific purpose where this approach was deemed usefull in the past.</p>
  
  <p>The case is supposed to only select a "then" when the column does have a number. However when i run it i get a invalid number ORA-01722. because one of the, rows will hold a string og date.</p>
  
  <p>I realise its properly because oracle asses the sql before executing, and doesnt execute sequential, therefore giving errors on these column even though it wouldn actually have to calculate on the column under a given ID.</p>
  
  <p>The code im trying to execute is the following, The hardcoded 1 and 2 before 'then' will change depending on ctrl_id (the unique id) and it will be the one securing that we only look and a list_val  column/row with a number </p>
  
  <pre><code>WITH sampledata1 AS
   (SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2
      FROM dual),
  sampledata2 AS
   (SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2
      FROM dual),
  sampledata3 AS
   (SELECT *
      FROM sampledata1
    UNION
    SELECT *
      FROM sampledata2)
  SELECT CASE
            WHEN ctrl_id = 1 THEN
             AVG(list_val1)
             over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
            WHEN ctrl_id = 2 THEN
             AVG(list_val2)
             over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
          END AS avg_val
    FROM sampledata3 qd
  </code></pre>
  
  <p>Any suggestions to how i can make this work. either a workaround or a different approach ? </p>
  
  <p>Thx in advance.</p>
  
  <p>--------- Solution below</p>
  
  <p>I used some of the suggestions and solutions posted below and got this code samble working. I will try and implement it with the system. Thx for the help everyone you saved me alot of headache.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code> WITH sampledata1
       AS (SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2 FROM DUAL),
       sampledata2
       AS (SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2 FROM DUAL),
       sampledata3
       AS (SELECT * FROM sampledata1
           UNION
           SELECT * FROM sampledata2)
   select ctrl_id,
   avg(CASE WHEN TRIM(TRANSLATE(list_val1, ' +-.0123456789', ' ')) is null 
   then list_val1 else null end) over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC) list_val1,
      avg(CASE WHEN TRIM(TRANSLATE(list_val2, ' +-.0123456789', ' ')) is null 
   then list_val2 else null end) over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC) list_val2
         
              from   sampledata3 qd</code></pre>
  </div>
  </div>
  </p>