Why ORA-00932: Inconsistent Types error?

Question

Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2.

The following query raise ORA-00932 error:

SELECT BBB.FIELD1 FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

However, the following query works perfectly fine.

SELECT * FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

The only difference is that I selected all (*) columns instead of specifying one specific column. Why is this happening?

EDIT

The error raised is (exactly as it is):

ORA-00932: inconsistent datatypes: expected  got 
ORA-02063: preceding line from DBLINK
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Answer 1

When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'". The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query. Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;

Answer 2

In my case, I used SUBSTR() over a CLOB column and used the result to compare it with another column that was VARCHAR and I got the error because the result of the SUBSTR was considered a CLOB. I used ASCIISTR(SUBSTR(...)) and it worked.