I have a query where I"m trying to return an id by filtering on a nvarchar2(2000) column. If I hard code one of the filter conditions, the query returns the desired output, but when using a subquery, I get the ORA-01858 error:

This query returns correct result:

SELECT * FROM (
SELECT atrb_val_tx as atrb_val_tx
FROM table1 a1
WHERE atrb_id in (666,669,672,675,688,694)
AND is_actv_fl=1) a1
WHERE to_date(regexp_substr(a1.atrb_val_tx,'[^ | ]+',1) ,'MM/DD/YYYY') <= TO_DATE('10/11/2016','MM/DD/YYYY');


But when substituting for the first statement in the WHERE clause like so, it returns ORA-01858
(note both inline views return the same data):

SELECT * FROM (
SELECT atrb_val_tx as atrb_val_tx
FROM table1 a1
WHERE atrb_id in (select atrb_id from table2 where ctrl_type_id=10)
AND is_actv_fl=1) a1
WHERE to_date(regexp_substr(a1.atrb_val_tx,'[^ | ]+',1) ,'MM/DD/YYYY') <= TO_DATE('10/11/2016','MM/DD/YYYY');

SQL> create table t ( x int, y varchar2(20));

Table created.

SQL>
SQL> insert into t values (1, '01/01/2000');

1 row created.

SQL> insert into t values (2, 'garbage');

1 row created.

SQL>
SQL> select *
  2  from t
  3  where to_date(y,'dd/mm/yyyy') < sysdate
  4  and x = 1;

         X Y
---------- --------------------
         1 01/01/2000

1 row selected.

SQL>
SQL> select *
  2  from t
  3  where to_date(y,'dd/mm/yyyy') < sysdate
  4  and x = ( select count(*) from dual );
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select *
  2  from t
  3  where ( y like '__/__/____' and to_date(y,'dd/mm/yyyy') < sysdate  )
  4  and x = ( select count(*) from dual );

         X Y
---------- --------------------
         1 01/01/2000