ora-01722 invalid number using count(1) on minus operator of two views

Question

I've got an error ora-01722: invalid number. Tried to_char, cast functions, nothing helps. I've got the following SQL, where PERSON_DOCS and PERSON_DOCS_NEW are the views and the field DOCTYPENAME is calculated using subselect inside the views and is of type varchar2(70), and is null for all rows in both views.

    select count(1) from 
     (select DOCTYPENAME
        from PERSON_DOCS 
      minus 
      select DOCTYPENAME
        from PERSON_DOCS_NEW)

Views at the moment are identical:

CREATE OR REPLACE VIEW PERSON_DOCS AS
  select

...,

(select lr.ll_remark
   from table1 ln,
        table2 lv,
        table3 lr
  where ln.short_name = 'DOC.TYPE'
    and ln.table_no = lv.table_no
    and lv.table_no = lr.table_no
    and lv.vetting_table = c.doc_type) as DOCTYPENAME,
 
...

from table0 c

Tried to_char, cast functions on the subselect inside the view, doesn't help.

Table3 DDL statement, from which the value comes:

CREATE TABLE table3
   (TABLE_NO VARCHAR2(35 BYTE), 
    LL_REMARK VARCHAR2(70 BYTE)
   ) ;

CREATE TABLE table1
   (TABLE_NO VARCHAR2(35 BYTE), 
    SHORT_NAME VARCHAR2(15 BYTE)
   );

CREATE TABLE table2
   (TABLE_NO VARCHAR2(35 BYTE), 
    VETTING_TABLE VARCHAR2(35 BYTE), 
    APPLICATION VARCHAR2(40 BYTE)
   );

CREATE TABLE table0
   (CUSTOMER_CODE VARCHAR2(10 BYTE),
    DOC_TYPE NUMBER
   );

Answer 1

Somewhere in your query there is an implicit type conversion. Just use explain plan of your query and in the predicates section or in the projection you will see something like to_number(..) or internal_function() .