Finding next first series in oracle query

Question

0.00/5 (No votes)

See more:

Hi.

How can i find in invoice series after missig sequence first serie.

Like this:

A402020000000001
A402020000000002
A402020000000006

İ Have codes for finding missing sequence series for example
(A402020000000003-A402020000000004-A402020000000005)
my question answer is must be ;

for A402020000000003 serie i must find A402020000000006 (next first seri)
for A402020000000004 serie i must find A402020000000006 (next first seri)
for A402020000000005 serie i must find A402020000000006 (next first seri)

What I have tried:

NEXTFIRST = "SELECT MAX(SERIE) FROM TABLE WHERE SERIE =LPAD(A.SERIE,7)||LPAD((TO_NUMBER(SUBSTR(A.SERIE,8,9))+1),9,0) "

in this code i find next seri only for

A402020000000005

result is

A402020000000006

others A402020000000003 and A402020000000004 is null

Posted 26-Sep-20 0:15am

Member 14588284

Updated 26-Sep-20 7:00am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Answer 1 · 2020-09-26T07:00:00

Solution 1

Once you issue an "invoice number" it should never go "missing". You have "outstanding" invoices as indicated by their balances in the accounts receivable or payables ledger.

If invoices are "missing", then your accounting system is corrupt.

Then you generate a list of "should have" invoice numbers and compare that with the ones that are "not missing"; e.g. select xxx not in yyy.

Posted 26-Sep-20 7:00am

Gerry Schmitz

Comments

Member 14588284 26-Sep-20 14:42pm

If invoices are "missing", then your accounting system is corrupt.

Already Thats the my problem. So i want find missing sequence and first serie after the missing sequence serie