title: ORA-01722 invalid number though types should match
body: <p>table1 dwh.fct_nc_crm_dims@etl4</p>
  
  <pre><code>Name                        Null     Type          
  --------------------------- -------- ------------- 
  BAN_KEY                     NOT NULL NUMBER(9)     
  CLIENT_NAME                          VARCHAR2(300) 
  CLIENT_INN                           VARCHAR2(40)  
  EFFECTIVE_DATE              NOT NULL DATE          
  EXPIRATION_DATE                      DATE   
  </code></pre>
  
  <p>table2 etl.stg_acrm_ban_attr@etl2</p>
  
  <pre><code>Name             Null Type          
  ---------------- ---- ------------- 
  SEGMENT_CRM           VARCHAR2(150) 
  BAN_KEY               VARCHAR2(32)  
  </code></pre>
  
  <p>table3 evkuzmin_b2b_churn_ban_segment</p>
  
  <pre><code>Name        Null Type          
  ----------- ---- ------------- 
  BAN_KEY          NUMBER(9)     
  CLIENT_NAME      VARCHAR2(300) 
  CLIENT_INN       VARCHAR2(40)  
  SEGMENT_CRM      VARCHAR2(150) 
  </code></pre>
  
  <p>My query. Here I join first 2 tables on ban_key and insert the result into the third table. The types match, but I still get the error. Why?</p>
  
  <pre><code>INSERT INTO evkuzmin_b2b_churn_ban_segment
  SELECT a.ban_key, a.client_name, a.client_inn, b.segment_crm FROM(
    SELECT ban_key, client_name, client_inn
    FROM(
      SELECT ban_key,client_name, client_inn, 
        ROW_NUMBER() OVER (PARTITION BY ban_key, client_inn ORDER BY effective_date DESC) AS rn
      FROM dwh.fct_nc_crm_dims@etl4 
      WHERE expiration_date &gt;= TO_DATE('01.04.2016','DD.MM.YYYY') OR expiration_date IS NULL --1ST DAY OF REPORTING PERIOD
    )
    WHERE rn = 1
  ) a, etl.stg_acrm_ban_attr@etl2 b 
  WHERE a.ban_key = b.ban_key;
  </code></pre>