Selecting gaps in database

Question

Selecting gaps in database

Navigation

#1 by (0 votes)

-1

I am trying to mount a select that finds missing items in a database. This is super unusual, 'select' items that do not exist. rsrs Let's say I have a table called TBL795 with the NRBEM column that should, in theory, not have gaps in the sequence of registered items.

It should look like this:

Registerednumbersareinsequence.

Ifyoulooklikethis:

This is wrong because some items have not been registered.

In a table with thousands of items it is difficult for you to check by select if you are missing an item and what are the missing items.

One way 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 from 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

Create a virtual table in memory I would like to do 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 does not work.

So

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, it does not give error. But does not bring missing items, it brings all the data from the virtual table.

Summary : can not I make this select with this virtual 'table', without having to physically create and feed it?

I remember doing something like this, but now I do not know where that select is. :

sqlite sqlite3

asked by anonymous 27.12.2016 / 16:39

1 answer

login example with Basic Athentication for swift? How to do when clicking submit, open a popup [duplicate]

score 0 · Answer 1

Before even posting the question I found out where I was going wrong. I posted because it might be useful to someone else.

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

This 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 check the missing platelets between items from 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 )