Re: Missing numbers
От | Simon Riggs |
---|---|
Тема | Re: Missing numbers |
Дата | |
Msg-id | 1117585288.3844.834.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Missing numbers (Harald Fuchs <use_reply_to@protecting.net>) |
Ответы |
Re: Missing numbers
Re: Missing numbers |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: