## Finding Holes in Numerical Sequence

1240 pts.
Tags:
Access Datasheet
AS400 iseries
SQL
SQL Query
I need help in identifying the missing numbers in a large query. The only problem is that some of the numbers may be alpha and numerical combined or not-they are mixed. Yet all the numbers are in the same column.  The query looks like this SELECT MSPMP100.PRDNO,MSPMP100.CLASS,MSPMP100.DESCP     FROM CARF1.MSPMP100 MSPMP100                                          Where MSPMP100.CLASS= ‘72′ MSPMP100.PRDNO is the field that would have the part numbers.  and the DESCP is the one that would have the description (but there seems not to be any order to the way the description are entered) So I need to find the holes in the PRDNO and at the same time sort the entire list by the make and models of the DESCP....? Any input would be greatly appreciated

Software/Hardware used:
MSXP,Professional,Vers2002,SP3

Thanks. We'll let you know when a new response is added.

So Tom or anyone out there …what if I would generate a query that would bring all that I need into a table. Then use a macro to remove all the unwanted alpha codes. Then some how link that new data to the hole finding query? Anyone have any suggestion or added ideas?

## Discuss This Question: 15 Replies

Thanks. We'll let you know when a new response is added.
• do you mean, if there were records for prdno = 1, 2, and 5 that you want a query that would tell you 3 and 4 didn't exist?
report
• I would like to have list of all the numbers that are missing if that is possible. Or have a list of all the numbers those present or something showing that that there is a hole in the sequence or the order. 4, 5, 9, - list of all the numbers missing -or- 1, 2, 3, (4), (5), 6, 7, 8, (9), 10 a list of all the numbers missign and present -or- 1, 2, 3, _, _, 6, 7, 8, _, 10 a list of numbers and showing just the holes. I would like to know if any or all are possible?
report
• My ultimate goal is to be able to down load a query for the AS400 system into an access from that would show the next open sequence number avalible in different groups. All the dfferent groups would be visible too. Each of the groups would show the next avalible number.
report
• Take a look at this similar question. It might help you.
report
• I will review this and work on it and comment back in a day or two ...thanks
report
• As I was looking at the string of information....as I began to review the different discussions, I started to wounder if this is using the AS400 Iseires SQL platform? Most of my means of commuication to the server is via passthrought query using MS Access.97 and I do have the ability of use office 2000 or newer. I have not used the Iseireis SQL as much...but could if that is what we are using as a platform there. With a little instruction I could do it.
report
• I started to wounder if this is using the AS400 Iseires SQL platform? The article linked by Carlosdl is indeed referring to iSeries. But the SQL is standard, with only the syntax of naming the tables being iSeries names. This is also standard SQL and might give you some ideas:
```select * from
(
SELECT 'Start hole' as Hole, (SEQ1 + 1) as NextSeq FROM myseqtbl
WHERE not (SEQ1 + 1) in (SELECT seq1 FROM myseqtbl)
union
SELECT 'End hole  ' as Hole, (SEQ1 - 1) as LastSeq FROM myseqtbl
WHERE not (SEQ1 - 1) in (SELECT seq1  FROM myseqtbl)
) as holetbl
order by 2,1 desc```
It assumes that you have a table named "myseqtbl" that has a column named SEQ1. It will give a list of the beginning and end of every hole in "myseqtbl". The UNION has two SELECTs. The first SELECT finds the start of every hole, and the second SELECT finds the end of every hole. The UNION and the ORDER BY put the two results together in a more useful order for viewing. There are two special cases that can be ignored. If your sequence begins at 1, it lists 0 as the end of the first hole. And also it lists the next sequence after the last one in your table as the start of the last hole. It does not list sequence numbers in the middle of holes. I.e., if a hole consists of sequence numbers (5, 6, 7 and 8), it won't list (6 and 7). It only lists (5) as the start and (8) as the end. Tom
report
• So all I need to do is to replace "myseqtbl' with "MSMMP100" and "SEQ1' with my 'PRDNO', since MSPMP100 is my table and PRDNO is my field
report
• Tom Liotta Thanks for the help. I have been reviewing and looking at the SQL statement. My plan of action with this was to setting up a query to pull the information from the AS400 server. I realized that my first query above already has generated the complete list of numbers that are stored in the AS400 server. So all I need to do is set up a query that would pull for the query shown above?
report
• Tom Liotta One other issue is ....there are some numbers in this list that have letters either mixed in with the numbers or precede the number or as a suffix. Is there a way to remove or pull the alpha codes out of the number so I am left with just raw numbers?
report
• Here is what I have put together based on the SQL statement you mentioned. select * from (SELECT `Start hole' as Hole, (prdno + 1) as NestSeq FROM MS0802allWapplication WHERE not (prdno + 1) in (SELECT prdno FROM MS0802allWapplication) union SELECT `End hole` as Hole, (prdno - 1) as LastSeq FROM MS0802allWapplication WHERE not (prdno -1) in (SELECT prdno FROM MS0802allWapplication) ) as holetbl order by 2,1 desc When I go to run it the error show on the the word SELECT right after the first From ( Select Now is it an issue if I am run Access 95 Querys