Обсуждение: Missing numbers

Hello list,

I need to track down the missing check numbers in a serie, table
contains a column for check numbers and series like this:

dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
dbalm-# and doc_tipo='CHE' order by doc_numero;
  doc_numero | doc_ckseriesfk
------------+----------------
  19200      |         856938
  19201      |         856938
  19215      |         856938
  19216      |         856938
  19219      |         856938

Missing numbers are:
from 19202 to 19214 and 19217,19218


Does anyone knows a way to get that done in SQL or plpgsql, thanks in
advance


--
Sinceramente,
Josué Maldonado.

... "De hecho el paso de compilación a objeto suele atravesar una fase
intermedia en que se genera un fichero en lenguaje ensamblador y se
invoca al programa ensamblador del sistema." -- Tutorial de C

In article <429C7B9B.1040705@lamundial.hn>,
josue <josue@lamundial.hn> writes:

> Hello list,
> I need to track down the missing check numbers in a serie, table
> contains a column for check numbers and series like this:


> dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
> dbalm-# and doc_tipo='CHE' order by doc_numero;
>   doc_numero | doc_ckseriesfk
> ------------+----------------
>   19200      |         856938
>   19201      |         856938
>   19215      |         856938
>   19216      |         856938
>   19219      |         856938

> Missing numbers are:
> from 19202 to 19214 and 19217,19218


> Does anyone knows a way to get that done in SQL or plpgsql, thanks in
> advance

You could use something like that:

SELECT g.num
FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
                      (SELECT max(doc_numero) FROM bdocs)) AS g(num)
LEFT JOIN bdocs ON bdocs.doc_numero = g.num
WHERE bdocs.doc_numero IS NULL

On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:
> In article <429C7B9B.1040705@lamundial.hn>,
> josue <josue@lamundial.hn> writes:
>
> > Hello list,
> > I need to track down the missing check numbers in a serie, table
> > contains a column for check numbers and series like this:
>
>
> > dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
> > dbalm-# and doc_tipo='CHE' order by doc_numero;
> >   doc_numero | doc_ckseriesfk
> > ------------+----------------
> >   19200      |         856938
> >   19201      |         856938
> >   19215      |         856938
> >   19216      |         856938
> >   19219      |         856938
>
> > Missing numbers are:
> > from 19202 to 19214 and 19217,19218
>
>
> > Does anyone knows a way to get that done in SQL or plpgsql, thanks in
> > advance
>
> You could use something like that:
>
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> WHERE bdocs.doc_numero IS NULL

SELECT g.num
FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
                      (SELECT max(doc_numero) FROM bdocs)) AS g(num)
WHERE g.num NOT IN (select doc_numero
           from bdocs
           where doc_numero is not null)

is more likely to return a correct answer, since
bdocs.doc_numero will never equal g,num when it is also NULL

Best Regards, Simon Riggs

On Wed, Jun 01, 2005 at 01:21:28AM +0100, Simon Riggs wrote:
> On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:

> > SELECT g.num
> > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> >                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> > WHERE bdocs.doc_numero IS NULL
>
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> WHERE g.num NOT IN (select doc_numero
>            from bdocs
>            where doc_numero is not null)
>
> is more likely to return a correct answer, since
> bdocs.doc_numero will never equal g,num when it is also NULL

Oh, but it is an outer join, so it should generate the NULLs, yes?

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

On Wed, 2005-06-01 at 00:27 -0400, Alvaro Herrera wrote:
> On Wed, Jun 01, 2005 at 01:21:28AM +0100, Simon Riggs wrote:
> > On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:
>
> > > SELECT g.num
> > > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> > >                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > > LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> > > WHERE bdocs.doc_numero IS NULL
> >
> > SELECT g.num
> > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> >                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > WHERE g.num NOT IN (select doc_numero
> >            from bdocs
> >            where doc_numero is not null)
> >
> > is more likely to return a correct answer, since
> > bdocs.doc_numero will never equal g,num when it is also NULL
>
> Oh, but it is an outer join, so it should generate the NULLs, yes?

You have a point, but so do I.

I wonder what the SQL spec should happen in this case? It depends upon
whether the NOT NULLs are excluded before or after the join takes
place.

If the WHERE clause said bdocs.doc_numero > 7 we would hope that this
was applied before the join. The correct answer, in that case, would
result whether we applied such a WHERE clause before or after the join.

But a WHERE clause that specifically disagrees with a join clause is
harder, and I would imagine we don't have a specific test for such a
thing, other than to exclude the push-down of the clause before the join
in all cases.

Perhaps we should test this...

Either way, I still prefer my phrasing of the SQL, which seems clearer,
but I would say that wouldn't I?

Best Regards, Simon Riggs

In article <1117618428.3844.914.camel@localhost.localdomain>,
Simon Riggs <simon@2ndquadrant.com> writes:

> If the WHERE clause said bdocs.doc_numero > 7 we would hope that this
> was applied before the join.

Stating this would change the OUTER into an INNER JOIN, and this would
imply that the order of the restrictions is irrelevant - for the
result set both conditions must be satisfied.

Simon Riggs wrote:
>>
>>You could use something like that:
>>
>>SELECT g.num
>>FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>>                      (SELECT max(doc_numero) FROM bdocs)) AS g(num)
>>LEFT JOIN bdocs ON bdocs.doc_numero = g.num
>>WHERE bdocs.doc_numero IS NULL
>
>
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> WHERE g.num NOT IN (select doc_numero
>            from bdocs
>            where doc_numero is not null)
>
> is more likely to return a correct answer, since
> bdocs.doc_numero will never equal g,num when it is also NULL

Yes, it worked pretty fine. Thanks.

--
Sinceramente,
Josué Maldonado.

... "Cultura es el paso que nos queda después de haber olvidado todo lo
aprendido."

you can also try this =)

select
  o1.doc_numero+1 as first,
  ((select doc_numero from bdocs where id > o1.doc_numero+1 order by doc_numero limit 1))-1 as last
from bdocs as o1
where o1.doc_numero+1 not in (select o2.doc_numero from bdocs as o2)
order by doc_numero




--
Verba volent, scripta manent
My ISP  - http://www.netbynet.ru

Simon Riggs <simon@2ndquadrant.com> writes:
> On Wed, 2005-06-01 at 00:27 -0400, Alvaro Herrera wrote:
>>> SELECT g.num
>>> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>>> (SELECT max(doc_numero) FROM bdocs)) AS g(num)
>>> LEFT JOIN bdocs ON bdocs.doc_numero = g.num
>>> WHERE bdocs.doc_numero IS NULL

> I wonder what the SQL spec should happen in this case? It depends upon
> whether the NOT NULLs are excluded before or after the join takes
> place.

The spec says that WHERE is logically applied after the join.  In some
cases it is possible to push the condition down to occur before the join
without changing the results ... but not in the above case.

> Either way, I still prefer my phrasing of the SQL, which seems clearer,
> but I would say that wouldn't I?

Maybe so.  The outer-join-and-test-for-null is a pretty common idiom
though, so I'd expect experienced SQL programmers to recognize it on
sight.

            regards, tom lane