Finding Holes in Numerical Sequence

1185 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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    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?
    8,200 pointsBadges:
    report
  • CompEng
    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?
    1,185 pointsBadges:
    report
  • CompEng
    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.
    1,185 pointsBadges:
    report
  • carlosdl
    Take a look at this similar question. It might help you.
    69,365 pointsBadges:
    report
  • CompEng
    I will review this and work on it and comment back in a day or two ...thanks
    1,185 pointsBadges:
    report
  • CompEng
    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.
    1,185 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • CompEng
    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
    1,185 pointsBadges:
    report
  • CompEng
    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?
    1,185 pointsBadges:
    report
  • CompEng
    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?
    1,185 pointsBadges:
    report
  • CompEng
    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
    1,185 pointsBadges:
    report
  • TomLiotta
    Access 95 Querys I don't know what language Access 95 uses. If it is a standard SQL language, it either implements only a basic subset or is too old. If it doesn't follow SQL standards, perhaps it has implementation requirements that would accept some changes to the statement. Does it support a SQL UNION?
    125,585 pointsBadges:
    report
  • TomLiotta
    there are some numbers in this list that have letters either mixed in with the numbers or precede the number or as a suffix. Well, all bets are off. If there are characters that aren't digits in the column, you don't have "numbers". You could create a function that pulls digits out character by character and generates a resulting "number". By the time you got that working and then integrated in with an Access 95 query, basic programming on the host server would have long since been finished with the whole process. Inclusion of any kind of qualifying characters into a "number" sequence implies that the characters have a degree of implicit meaning. For example, there might be a part number like "01234". Someone might later realize that the part can have modifications that result in "right-side" and "left-side" versions of the basic product, and those are created as parts "01234R" and "01234L". There are then three distinct part numbers. But those extra characters carry meaning by themselves. Fundamental database design rules would indicate that the extra characters ought to be in a separate column rather than embedded in an existing column. One reason for doing so is to avoid ever needing to do just what you have been tasked with doing. If the column isn't numeric, then the concept of "holes" becomes a mess. It isn't very meaningful. How many holes are there between "1234A", "1234B", "B1234" and "01234"? Tom
    125,585 pointsBadges:
    report
  • CompEng
    Due to my lack of knowledge in SQL ...that is why I am asking this...in MS Excel there is a function called "Text To Column" and there are two different way that this function can seperate "Delimited" or "Fixted" . Is there a way that this or anything command or fuction in SQL that would give me the same results. A command that looks at what is in each cell in a column and take each character and move them into different location across the rows?
    1,185 pointsBadges:
    report
  • TomLiotta
    Is there a way that this or anything command or fuction in SQL that would give me the same results. The practical answer is "No." Such manipulation can be possible by very experienced SQL developers who can create intermediate views, functions, etc.; but even then it is complex and time consuming. It simply isn't what SQL is intended to do. You're effectively needing a programming solution rather than a database query solution. One basic approach in SQL could be first to mask out non-digits with TRANSLATE(). The non-digit characters would be translated to blanks or any other fixed character that wasn't a digit; this character would be used as a delimiter. The masked value might then be processed recursively to locate substrings of digit characters between the delimiters. Each succeeding substring would be concatenated with the value from the previous recursion cycle. The cycles would end when no digit substrings could be located. The resulting string of digits could then be cast as a numeric value. I'm not sure that any recursive processing could actually be implemented for that, but some kind of recursion is indicated to locate "letters ... mixed in with the numbers". I suspect that it might be possible to implement a potentially horrible CASE structure if the pattern of mixed letters/digits is very limited. But some very detailed analysis needs to be done at the start to see if it's even feasible. Then again, someone out there just might have a bright idea that makes it easy. A deeply nested series of REPLACE() functions, for example, could replace individual letters with nulls. It'd be a really bad statement at the end, and if some initial values are all letters, the series should choke at some nesting level. Anyway, I suggest that this simply shouldn't be done at the query level. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following