Filter numbers not yet registered via SQL

Question

I’m trying to find the numbers that haven’t been entered in the bank yet.

I have a client table, in it I have the fields codigo and nome.

When the user registers the client, he puts the code manually, so he risks skipping some numbers.

Is there any way SQL for me to try to select these numbers that have not yet been registered?

Answer 1

It would even be simple to do that:

Do a search in the same table before adding a new record and take the return and add the code in an array and then add compare if the code entered by the user is within this array.

It’s a very simple way to do it.

Answer 2

Emerson,

To verify which codes were not registered in the range, you can execute the query below by changing the table name.

DECLARE @CONTADOR INTEGER
DECLARE @TT INTEGER

CREATE TABLE #CODIGO (
CODIGO INTEGER )

SET @CONTADOR = 1


WHILE ((SELECT MAX(CODIGO) FROM TABELA) >= @CONTADOR)
BEGIN
    SET @TT = (SELECT COUNT(CODIGO) FROM TABELA WHERE CODIGO = @CONTADOR)

IF @TT = 0
BEGIN
INSERT INTO #CODIGO VALUES (@CONTADOR)
END

SET @CONTADOR = @CONTADOR + 1

END

SELECT * FROM #CODIGO
DROP TABLE #CODIGO

Answer 3

If you are using one SEQUENCE it is possible to predict what the next codigo to be inserted.

CREATE SEQUENCE codigoUsuario;

To select the code:

SELECT nextval('codigoUsuario');

When creating your table you can set the SEQUENCE directly in the field:

CREATE TABLE tabela ( codigo INTEGER DEFAULT nextval('codigoUsuario') );

I don’t think it’s a good idea to leave code control in the hands of the user.

Need to check if there is already a certain new code at the time of creation will only create more complexity in the application, which without a really feasible use case is simply unnecessary.

Answer 4

SELECT linha
  FROM (SELECT DECODE(codigo,linha,'N','S') AS  proximo
              ,linha
                 FROM (SELECT codigo
                             ,ROWNUM linha
                             ,nome 
                         FROM cliente
                        ORDER BY codigo))                          
 WHERE proximo = 'S'
   AND ROWNUM = 1

The above example I did with oracle, it makes the next number valid. If helped mark as right, thanks.