1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get missing number in sequence

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Mar 22, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I have a table A with column low_term_no and high_term_no
    Table B has term_no.

    I need to get the term_no from Table B where term_no is between low_term_no and high_term_no

    ex: Table A : low_term_no = 1 and high_tern_no = 10
    Table B : 1
    2
    3
    4
    5
    7

    Now the missing number is 6,8,9,10

    how to get the missing number. I would need to use the same logic in ODI
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,622
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A sequence is not guaranteed to be continuous and gap-free; any 'requirement' you have been given to ensure such behavior is wrong and needs to be discarded. Why do you feel the need to 'replace' missing sequence values? What purpose does that serve? I find no reason to pursue that quest.
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    It is basically for a report generation. It is a requirement :)
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,622
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    WHY is this a 'requirement'? What purpose does it serve? Three of the sequence values you report as 'missing' may not have been generated yet so they really AREN'T missing. What are you planning on doing with these 'missing' sequence numbers? Where will they go? WHY do you 'need' them? There are many unanswered questions with this request and no good reason to pursue it.