Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Find missing sequence numbers

    I have an issue to solve.... Any help would be nice

    I have incoming files that contain an embedded sequence number from a vendor indicating the sequence if the file received. I have been asked to check for missing sequences as I load the data. These are the requirements.

    1. Ignore the last # files (currently set to 8) in order to account for staggered arrival of the files. In other words do throw an error if the there is a missing files older than the last e.g. 8 of files.
    2. Sequences start with 1 up to 99999 and then rollover back to 1. This will likely require 2 queries when the 99999 is found (1 for those <= 99999 and 1 for >= 1).

    I already have a query that indicates both the min and max plus the min/max when the 99999 is included. Kind of funky but.... shrug


    SELECT
    MIN(file_seq) AS min_seq,
    MAX(file_seq) AS max_seq,
    MAX(
    CASE WHEN file_seq <= 8
    THEN file_seq
    ELSE 0
    END
    ) AS p_max,
    MIN(
    CASE WHEN file_seq >= 99999 - 8
    THEN file_seq
    ELSE null
    END
    ) AS p_min
    FROM load_log
    WHERE file_date >= TRUNC(SYSDATE-30)
    /
    MIN_SEQ MAX_SEQ P_MAX P_MIN
    --------- ---------- ---------- ----------
    1 99999 8 99991


    MIN_SEQ MAX_SEQ P_MAX P_MIN
    --------- ---------- ---------- ----------
    1 8 8


    3. I will be limiting the query to the last 30 days and the rollover of the numbers only occurs once every 1.5 years. The files also contain an embedded date.
    4. I am implementing a "missing_file" table to store those sequences that are missing and will check against that list if the "missing" file arrives. If it does arrive then that row will be deleted from the table.
    5. This is being implemented on a 11.1 DB.
    6. Currently I am working with a package to handle this but it get more complicated as I go with all the conditions, so I am looking for a way to simplify using a query to get prospective missing numbers.

    BTW. I have been working with Oracle since v. 6.0 so please don't reply if all you want to do with tell me I don't understand Oracle..

    ---------------------------------------------------
    Upgraded: Didn’t work the first time
    Upgraded and Improved: Didn’t work the second time
    ---------------------------------------------------
    Last edited by mwinterer; 04-08-13 at 19:58.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select all_valid_seq_values_within_last_30_days_minus_8 from row_generator
    minus
    select file_seq
    FROM load_log
    WHERE file_date >= TRUNC(SYSDATE-30);

    The topmost select returns all valid/possible sequence_value to within 8 of MAX(FILE_SEQ); which will result in any that are missing to be in result set.

    I acknowledge that proposed solution above ignores the roll over case.
    First make it work; then make it fancy.
    Last edited by anacedent; 04-08-13 at 20:42.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •