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

Improve this question
6
  • All rows have same format, i want to get missing rows Commented Apr 1, 2021 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 Commented Apr 1, 2021 at 13:26
  • c_order_id is primary key Commented Apr 1, 2021 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. Commented Apr 1, 2021 at 13:46
  • i want to find record that existed but was deleted like YD4BC2006008 or DGPOS2003030, DGPOS2003031,DGPOS2003032.......DGPOS2003068 for example Commented Apr 1, 2021 at 13:49

2 Answers 2

Reset to default
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

Improve this answer
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;
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.