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
In this example many rows missing: from 'DGPOS2003030'
to 'DGPOS2003068'
.
How do I write a query to retrieve them?
Best Answer
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
db<>fiddle here