Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: how do i find next available value?

    Hi,

    This is my first post and I am a beginner so please be gentle.

    Ok, im trying to select a value that is sequential however some will have been missed/deleted from a VARCHAR2 column leaving gaps that can be utilised. I have managed to do this before using the SQL below however this is specific to a NUMBER so will not work when performing it on a VARCHAR2 value. Any ideas?

    SELECT min( a.column1 + 1) column1
    FROM table1 a
    WHERE NOT EXISTS
    (SELECT 1
    FROM table1 b
    WHERE b.column1 = ( a.column1 + 1) )
    AND a.column1 NOT IN (select MAX(c.column1) FROM table1 c)
    ORDER BY column1;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why do you feel you need to reuse values?

    How would you come up with the next value if you were adding it at the "end"?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    How do you define "gap" between two VARCHAR2 values?
    If it contains numeric part and you want to detect gap between it, extract the numbers e.g. by using SUBSTR function. Using TO_NUMBER for explicit conversion to NUMBER data type would be best option.
    Otherwise, it is impossible without the rule for detecting gap (some sample data would be great for understanding). Generally, how would you say, whether there is a gap e.g. between A, AA, AAA, AAZ, AB, ABA, ABBA, ABC values and (the most important part) which values are missing?

Posting Permissions

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