0

I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing.

I want to find the missing lines via c_order_id and documentno.

I tried this but it didn't return the expected result:

select * from
(SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no
FROM adempiere.c_order
ORDER BY documentno, c_order_id) as f

enter image description here

In this example many rows missing: from 'DGPOS2003030' to 'DGPOS2003068'.

How do I write a query to retrieve them?

enter image description here

6
  • All rows have same format, i want to get missing rows – Franck White Apr 1 at 13:24
  • I tried code select * from (SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no FROM adempiere.c_order ORDER BY documentno, c_order_id) as f but it is not finich – Franck White Apr 1 at 13:26
  • c_order_id is primary key – Franck White Apr 1 at 13:39
  • Does the record YD4BC2006008 exist, or are you trying to find the records that don't exist? If that's what you're after, then Colin's answer below should serve. – Darwin von Corax Apr 1 at 13:46
  • i want to find record that existed but was deleted like YD4BC2006008 or DGPOS2003030, DGPOS2003031,DGPOS2003032.......DGPOS2003068 for example – Franck White Apr 1 at 13:49
0

A more dynamic approach would be , getting the lowest Number and the highest and building the m missing from there.

If you don't have a leading 2 like in the example 2006004 you need to pad the generated part

CREATE TABLE documents (
  "order_id" VARCHAR(22),
  "documentno" VARCHAR(12)
);

INSERT INTO documents
  ("order_id", "documentno")
VALUES
  ('100001110', 'VD4BC2006004'),
  ('100001114' ,'VD4BC2006005'),
  ('100001135' ,'YD4BC2006006'),
  ('100001166' ,'YD4BC2006007'),
  ('100001215' ,'YD4BC2006009'),
  ('100001256' ,'YD4BC2006010'),
  ('100001289' ,'YD4BC2006011'),
  ('100001332' ,'VD4BC2006013'),
  ('100001334' ,'VD4BC2006014'),
  ('100001432' ,'VD4BC2006015'),
  ('100000616' ,'DGP0S2003028'),
  ('100000617' ,'DGP0S2003029'),
  ('100001034' ,'DGP0S2003069');
WITH cte as (
SELECT LEFT("documentno",5) lpart , MIN(RIGHT("documentno",7)) minpart, MaX(RIGHT("documentno",7)) maxpart FROM documents
groUP BY LEFT("documentno",5))
sELECT lpart || generate_series(minpart::INTEGER, maxpart::INTEGER) as mdocumentno FROM cte
except
select documentno from documents
ORDER bY mdocumentno;
| mdocumentno  |
| :----------- |
| DGP0S2003030 |
| DGP0S2003031 |
| DGP0S2003032 |
| DGP0S2003033 |
| DGP0S2003034 |
| DGP0S2003035 |
| DGP0S2003036 |
| DGP0S2003037 |
| DGP0S2003038 |
| DGP0S2003039 |
| DGP0S2003040 |
| DGP0S2003041 |
| DGP0S2003042 |
| DGP0S2003043 |
| DGP0S2003044 |
| DGP0S2003045 |
| DGP0S2003046 |
| DGP0S2003047 |
| DGP0S2003048 |
| DGP0S2003049 |
| DGP0S2003050 |
| DGP0S2003051 |
| DGP0S2003052 |
| DGP0S2003053 |
| DGP0S2003054 |
| DGP0S2003055 |
| DGP0S2003056 |
| DGP0S2003057 |
| DGP0S2003058 |
| DGP0S2003059 |
| DGP0S2003060 |
| DGP0S2003061 |
| DGP0S2003062 |
| DGP0S2003063 |
| DGP0S2003064 |
| DGP0S2003065 |
| DGP0S2003066 |
| DGP0S2003067 |
| DGP0S2003068 |
| VD4BC2006006 |
| VD4BC2006007 |
| VD4BC2006008 |
| VD4BC2006009 |
| VD4BC2006010 |
| VD4BC2006011 |
| VD4BC2006012 |
| YD4BC2006008 |

db<>fiddle here

0
1

One easy way is to generate a list of all potential documentnos and remove from that all the ones that exist:

select 'YD4BC' || generate_series(2006004, 2006015) as missing_documentno
except
select documentno from documents;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.