Loading ...
Sorry, an error occurred while loading the content.

Missing element from an alphanumeric sequence

Expand Messages
  • Noman Aftab
    Dear All,   I have the following data:   USD00001 USD00002 USD00005 AER00003 AER00004 GBP00002   What I want is, if I give USD as an
    Message 1 of 4 , Aug 19, 2008
    • 0 Attachment
      Dear All,
       
      I have the following data:
       
      <CASH_ACCOUNT_NUMBER>
      USD00001
      USD00002
      USD00005
      AER00003
      AER00004
      GBP00002
       
      What I want is, if I give USD as an input to the function I want 'USD00003' (first missing element in the list for USD) as a return.
       
      I saw Arnie's post about "Incrementing an alphanumeric value" but that function only returns (MAX+1) or 1 if no such prefix is found.
      http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=IncrementAlphaNumericValues&referringTitle=Home


       
      Noman Aftab
      Software Engineer

       

      Send instant messages to your online friends http://uk.messenger.yahoo.com

      [Non-text portions of this message have been removed]
    • Arnie Rowland
      There are several performance reasons why trying to backfill a sequence is problematic. Usually, it is not a good idea to attempt to do what you desire.
      Message 2 of 4 , Aug 19, 2008
      • 0 Attachment
        There are several performance reasons why trying to 'backfill' a sequence is problematic. Usually, it is not a good idea to attempt to do what you desire. However, I realize that sometimes business requirements require 'unusual' solutions.

        You could use a query similar to this: (perhaps as a FUNCTION)

        SELECT ( right( m1.Cash_Account_Number, 5 ) + 1 ) AS 'Next_Cash_Account_Number'
        FROM @MyTable m1
        JOIN @MyTable m2
        ON m1.Cash_Account_Number < m2.Cash_Account_Number
        GROUP BY m1.Cash_Account_Number
        HAVING ( right( m1.Cash_Account_Number, 5 ) + 1 ) < MIN( right( m2.Cash_Account_Number, 5 ) )
        ORDER BY Next_Cash_Account_Number

        NOTE: This may have performance issues.

        Also, you may run into scalability and concurrency issues. I suggest that if you really need to go down this path, that the following may be useful:

        Lock a Stored Procedure for Single Use Only
        http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=GetAppLock


        Regards,

        Arnie Rowland, MVP (SQL Server)

        "I am a great believer in luck, and I find that the harder I work, the more I have of it."
        - Thomas Jefferson (1743-1826)


        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
        Sent: Tuesday, August 19, 2008 8:13 AM
        To: Arnie
        Subject: [SQLQueriesNoCode] Missing element from an alphanumeric sequence

        Dear All,

        I have the following data:

        <CASH_ACCOUNT_NUMBER>
        USD00001
        USD00002
        USD00005
        AER00003
        AER00004
        GBP00002

        What I want is, if I give USD as an input to the function I want 'USD00003' (first missing element in the list for USD) as a return.

        I saw Arnie's post about "Incrementing an alphanumeric value" but that function only returns (MAX+1) or 1 if no such prefix is found.
        http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=IncrementAlphaNumericValues&referringTitle=Home



        Noman Aftab
        Software Engineer



        Send instant messages to your online friends http://uk.messenger.yahoo.com

        [Non-text portions of this message have been removed]


        ------------------------------------

        Yahoo! Groups Links







        Disclaimer - August 19, 2008
        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
      • Noman Aftab
        Thanks Arnie, I only expect a few hundred records...say 500   Noman Aftab Software Engineer ... From: Arnie Rowland Subject: RE:
        Message 3 of 4 , Aug 19, 2008
        • 0 Attachment
          Thanks Arnie,
          I only expect a few hundred records...say 500
           
          Noman Aftab
          Software Engineer


          --- On Tue, 19/8/08, Arnie Rowland <arnie@...> wrote:

          From: Arnie Rowland <arnie@...>
          Subject: RE: [SQLQueriesNoCode] Missing element from an alphanumeric sequence
          To: SQLQueriesNoCode@yahoogroups.com
          Date: Tuesday, 19 August, 2008, 9:37 PM






          There are several performance reasons why trying to 'backfill' a sequence is problematic. Usually, it is not a good idea to attempt to do what you desire. However, I realize that sometimes business requirements require 'unusual' solutions.

          You could use a query similar to this: (perhaps as a FUNCTION)

          SELECT ( right( m1.Cash_Account_ Number, 5 ) + 1 ) AS 'Next_Cash_Account_ Number'
          FROM @MyTable m1
          JOIN @MyTable m2
          ON m1.Cash_Account_ Number < m2.Cash_Account_ Number
          GROUP BY m1.Cash_Account_ Number
          HAVING ( right( m1.Cash_Account_ Number, 5 ) + 1 ) < MIN( right( m2.Cash_Account_ Number, 5 ) )
          ORDER BY Next_Cash_Account_ Number

          NOTE: This may have performance issues.

          Also, you may run into scalability and concurrency issues. I suggest that if you really need to go down this path, that the following may be useful:

          Lock a Stored Procedure for Single Use Only
          http://code. msdn.microsoft. com/SQLExamples/ Wiki/View. aspx?title= GetAppLock

          Regards,

          Arnie Rowland, MVP (SQL Server)

          "I am a great believer in luck, and I find that the harder I work, the more I have of it."
          - Thomas Jefferson (1743-1826)

          -----Original Message-----
          From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab
          Sent: Tuesday, August 19, 2008 8:13 AM
          To: Arnie
          Subject: [SQLQueriesNoCode] Missing element from an alphanumeric sequence

          Dear All,

          I have the following data:

          <CASH_ACCOUNT_ NUMBER>
          USD00001
          USD00002
          USD00005
          AER00003
          AER00004
          GBP00002

          What I want is, if I give USD as an input to the function I want 'USD00003' (first missing element in the list for USD) as a return.

          I saw Arnie's post about "Incrementing an alphanumeric value" but that function only returns (MAX+1) or 1 if no such prefix is found.
          http://code. msdn.microsoft. com/SQLExamples/ Wiki/View. aspx?title= IncrementAlphaNu mericValues& referringTitle= Home

          Noman Aftab
          Software Engineer

          Send instant messages to your online friends http://uk.messenger .yahoo.com

          [Non-text portions of this message have been removed]

          ------------ --------- --------- ------

          Yahoo! Groups Links

          Disclaimer - August 19, 2008
          This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@ yahoogroups. com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
          This disclaimer was added by Policy Patrol: http://www.policypa trol.com/














          Send instant messages to your online friends http://uk.messenger.yahoo.com

          [Non-text portions of this message have been removed]
        • peternilsson42
          ... Recent versions of Oracle allow... with cash_accts as ( select USD00001 acct_no from dual union all select USD00002 acct_no from dual union all select
          Message 4 of 4 , Sep 2, 2008
          • 0 Attachment
            Noman Aftab <noman17pk@...> wrote:
            >
            > Dear All,
            >  
            > I have the following data:
            >  
            > <CASH_ACCOUNT_NUMBER>
            > USD00001
            > USD00002
            > USD00005
            > AER00003
            > AER00004
            > GBP00002
            >  
            > What I want is, if I give USD as an input to the function I
            > want 'USD00003' (first missing element in the list for USD)
            > as a return.

            Recent versions of Oracle allow...

            with
            cash_accts as
            (
            select 'USD00001' acct_no from dual union all
            select 'USD00002' acct_no from dual union all
            select 'USD00003' acct_no from dual union all
            select 'AER00003' acct_no from dual union all
            select 'AER00004' acct_no from dual union all
            select 'GBP00002' acct_no from dual
            )
            select min('&xxx' || lpad(x.n, 5, '0'))
            from ( select level n
            from dual
            connect by
            level <= (select count(*) + 1
            from cash_accts
            where acct_no like '&xxx%')) x
            where not exists (select 1
            from cash_accts
            where acct_no = '&xxx' || lpad(x.n, 5, '0'));

            --
            Peter
          Your message has been successfully submitted and would be delivered to recipients shortly.