Selecting gaps in database

Asked

Viewed 37 times

-1

I’m trying to build a select that will locate the missing items from a database. This is super unusual, 'select' the items that don’t exist. Let’s say I have a table called TBL795 with the column NRBEM that should, in theory, not have gaps in the sequence of registered items.

It must be so:

inserir a descrição da imagem aqui

The numbers registered are in sequence.

If so:

inserir a descrição da imagem aqui

this wrong, because some items were not registered.

In a table with thousands of items it is difficult to check by select whether an item is missing and which items are missing.

One of the ways would be to create a TEST table:

CREATE TABLE TESTE ( 
  NRBEM VARCHAR(15))

feed it with the command: 

INSERT INTO TESTE 
WITH RECURSIVE
  cnt(NRBEM) AS (VALUES(1) UNION ALL SELECT NRBEM+1 FROM cnt WHERE NRBEM <100000) 
SELECT NRBEM FROM cnt A

and run select 

SELECT A.NRBEM FROM TESTE A LEFT JOIN TBL795 B 
ON A.NRBEM = B.NRBEM
WHERE B.NRBEM IS NULL

i see all the items that are missing in my table.

Since the command:

  WITH RECURSIVE
      cnt(NRBEM) AS (VALUES(1) UNION ALL SELECT NRBEM+1 FROM cnt WHERE NRBEM <100000) 
    SELECT NRBEM FROM cnt

creates a virtual table in memory I would like to make the following select:

SELECT NRBEM FROM (
WITH RECURSIVE
  cnt(NRBEM) AS (VALUES(1) UNION ALL SELECT NRBEM+1 FROM cnt WHERE NRBEM <100000) 
SELECT NRBEM FROM cnt ) A LEFT JOIN TBL795 B
ON A.NRBEM = B.NRBEM

But it doesn’t work.

Thus 

SELECT X FROM (
WITH RECURSIVE
  cnt(X) AS (VALUES(1) UNION ALL SELECT X+1 FROM cnt WHERE X <100000) 
SELECT X FROM cnt ) A LEFT JOIN TBL795 B
ON A.X = B.NRBEM

ALMOST works, that is, there is no error. But doesn’t bring missing items, brings all data from the virtual table.

Summary: there is no way I can make this select with this virtual 'table' without having to physically create it and feed it?

I remember doing something like that, but now I don’t know where that select. :(

sqlite sqlite3

  • I posted the question, even though I found the answer a little earlier. Thinking about helping someone and I get negative votes. Frankly.

  • 1

    I wasn’t the one who said no, but I think your question was a little confusing. Help is nice, but for that you could make a little more clear what you want. Furthermore, the proposed solution depends a little on faith, as it has no explanation of how it works. I am a little afraid to comment on these things, because sometimes it is poorly received, but I think would be points that if improved, could value the two posts.

1 answer

0


Even before posting the question I found out where I was wrong. I posted because it might be useful for someone else.

I needed to turn my column into number to select the data correctly.

It works.

SELECT A.X FROM (
WITH RECURSIVE
  cnt(X) AS (VALUES(1) UNION ALL SELECT X+1 FROM cnt WHERE X <100000) 
SELECT X FROM cnt ) A LEFT JOIN ( SELECT CAST( NRBEM AS NUMBER ) AS NRBEM FROM TBL795 ) B
ON A.X = B.NRBEM
WHERE B.NRBEM IS NULL

It gets better:

SELECT A.X FROM (
WITH RECURSIVE
  cnt(X) AS (VALUES(1) UNION ALL SELECT X+1 FROM cnt WHERE X < ( select max( cast ( nrbem as number )) from tbl795 )) 
SELECT X FROM cnt ) A LEFT JOIN ( SELECT CAST( NRBEM AS NUMBER ) AS NRBEM FROM TBL795 ) B
ON A.X = B.NRBEM
WHERE B.NRBEM IS NULL

If I want to refer to the missing platelets among the items 2400 to 2700 

SELECT A.X FROM (
WITH RECURSIVE
  cnt(X) AS (VALUES(2400) UNION ALL SELECT X+1 FROM cnt WHERE X < (2700)) 
SELECT X FROM cnt ) A LEFT JOIN ( SELECT CAST( NRBEM AS NUMBER ) AS NRBEM FROM TBL795 WHERE NRBEM >= 2400 and nrbem <= 2700 ) B
ON A.X = B.NRBEM
WHERE B.NRBEM IS NULL
LIMIT ( 2700 - 2400 + 1 )

Browser other questions tagged sqlite sqlite3

You are not signed in. Login or sign up in order to post.