ORA-01722 invalid number - table join

Question

So i have this simple oracle query which returns me the expected record:

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) < 21
and to_number(substr(nr_key,5,4)) = 88

If I transform it into this:

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) < 21
) 
where numero = 88

It gives me the "ORA-01722 invalid number" error

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

The strangest thing is that if I comment the join, the query runs again:

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) < 21
) 
where numero = 88

And the join has nothing to do with te "numero" field...

As asked, i post here the tables:

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" ;

Any idea about it?

Answer 1

Thanks to the suggestions i got, after some tests, the only way I could get it work is this:

SELECT to_number(numero) FROM ( 
SELECT 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) < 21 
) 
where numero = lpad('88',4,'0')