In that case, you have different data than you posted in your initial post. For me, it is giving rows which ZONE_CODE column value is not present in any EMPLOYER_NO column. It would be really nice if you exactly described in words what is the result you want.
SQL> with m394aforms as ( select '15897/A/' EMPLOYER_NO, 'B' zone_code from dual
2 union all select '54687/B/', 'F' from dual )
3 -- your query with NOT IN displaying all result set rows
4 select *
5 from m394aforms
6 where ZONE_CODE not in (
7 select regexp_replace(EMPLOYER_NO,'[^A-Za-z]', '')
8 from m394aforms
When its not change then same EMPLOYER_NO letter will appear in ZONE_CODE
I have no idea what is this sentence supposed to mean. Sorry.
Originally Posted by ai_zaviour
What i need is the count of ZONE_CODE where is different than EMPLOYER_NO
And that is how many for those 15 rows? Which of them shall be counted and why?
If you want to compare those two values from the same row for non-equality, simply do not use any subquery and do it directly.
where ZONE_CODE != regexp_replace(EMPLOYER_NO,'[^A-Za-z]', '');
Maybe you will also need to treat the situation when no letters in EMPLOYER_NO column - just add an extra check whether the regular expression is NULL.