ORA-01722 invalid number - table join
<p>So i have this simple oracle query which returns me the expected record:</p>

<pre><code>SELECT to_number(substr(nr_key,5,4)) as numero
FROM fattura_pa 
 LEFT JOIN fattura_pa_status on status_id = id
WHERE ute = 'BX' and length(nr_key) &lt; 21
and to_number(substr(nr_key,5,4)) = 88
</code></pre>

<p>If I transform it into this:</p>

<pre><code>SELECT * FROM ( 
  SELECT to_number(substr(nr_key,5,4)) as numero
  FROM fattura_pa 
    LEFT JOIN fattura_pa_status on status_id = id
  WHERE ute = 'BX' and length(nr_key) &lt; 21
) 
where numero = 88
</code></pre>

<p>It gives me the "ORA-01722 invalid number" error</p>

<p>I cannot undrestand why, since my "numero = 88" is actually a number...</p>

<p>The strangest thing is that if I comment the join, the query runs again:</p>

<pre><code>SELECT * FROM ( 
  SELECT to_number(substr(nr_key,5,4)) as numero
  FROM fattura_pa 
  --LEFT JOIN fattura_pa_status on status_id = id
  WHERE ute = 'BX' and length(nr_key) &lt; 21
) 
where numero = 88
</code></pre>

<p>And the join has nothing to do with te "numero" field...</p>

<p>As asked, i post here the tables:</p>

<pre><code>CREATE TABLE "FE_ENGINE"."FATTURA_PA" 
   (    "UTE" VARCHAR2(2 BYTE) NOT NULL ENABLE, 
    "NR_KEY" VARCHAR2(21 BYTE) NOT NULL ENABLE, 
    "PROGRESSIVO_INVIO" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "XML_CREATO" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
    "XML_FILE_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
    "POSIZIONE" NUMBER(4,0) DEFAULT 1 NOT NULL ENABLE, 
    "D3_FILE_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
    "SPEDITA_IL" DATE, 
    "ACCETTATA_IL" DATE, 
    "ARCHIVIATA_IL" DATE, 
    "STATUS_ID" NUMBER(4,0) NOT NULL ENABLE, 
    "RIELABORARE" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
    "ROW_INSTIME" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
    "ROW_UPTIME" DATE, 
    "NR_KEY_OLD" VARCHAR2(20 BYTE), 
     CONSTRAINT "UQ_FATTURA_PA_FILE_NAME_POSIT" UNIQUE ("XML_FILE_NAME", "POSIZIONE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "D3_USR"  ENABLE, 
     CONSTRAINT "FATTURA_PA_PK" PRIMARY KEY ("UTE", "NR_KEY", "PROGRESSIVO_INVIO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "D3_USR"  ENABLE, 
     CONSTRAINT "FK_FATTURA_PA_FATTURA_PA_STATU" FOREIGN KEY ("STATUS_ID")
      REFERENCES "FE_ENGINE"."FATTURA_PA_STATUS" ("ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "D3_USR" ;

CREATE TABLE "FE_ENGINE"."FATTURA_PA_STATUS" 
   (    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "COD_STATUS" VARCHAR2(16 BYTE) NOT NULL ENABLE, 
    "DESC_STATUS" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
     CONSTRAINT "PK_FATTURA_PA_STATUS" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "D3_USR"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "D3_USR" ;
</code></pre>

<p>Any idea about it?</p>