Problem to select not in

Question

I want to display the values that are between 0 and 10 and dont exist in the current table

For example :

Field
1
2
3
5
9
8

Result expected:

7
4 

The problem is that the column field is a varchar, so i have first convert to number and then make the validations

SELECT TO_NUMBER(yy.ent_sub_dealer_code,'9') FROM a_entidades yy 
where TO_NUMBER(yy.ent_sub_dealer_code,'9') not in (SELECT LEVEL n
                                                   FROM DUAL
                                                   CONNECT BY LEVEL <= 9     )

Any suggestions?

Answer 1

create table t1 (x varchar2(2));

insert into t1 (x) values ('1');
insert into t1 (x) values ('2');
insert into t1 (x) values ('3');
insert into t1 (x) values ('5');
insert into t1 (x) values ('6');
insert into t1 (x) values ('8');
insert into t1 (x) values ('9');
insert into t1 (x) values ('10');
insert into t1 (x) values ('11');
commit;

SQL>  select level as x
  2     from dual
  3  connect by level <= 9
  4    minus
  5   select to_number(x)
  6     from t1;

         X
----------
         4
         7

2 rows selected.