Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Netherlands
    Posts
    4

    Question Unanswered: Help with Statement (PL/SQL)

    Hi,

    something to set your teeth into

    I know my way around in SQL but i need to convert this into PL/SQL and that's a little bit rusty

    I have a form with a certain ID field.

    The ID field is build like ABCD1234 (prefix + sequence)

    The user selects the Char field from a lookup table. The length is 3 or 4 characters.

    The number is supposed to be a sequence.

    I want to select the last number that starts with that prefix and increase the sequence with 1.

    I've gathered the following SQL statements that i would need. The 'DOCU%' part is supposed to be a variable (selected from screen).

    ----------------------------------SQL Statements ------------------------------

    Get highest ITEM_ID
    ------------------------

    select max(item_id)
    from items
    where item_id like 'DOCU%'

    --> 'DOCU0006'


    Length of String
    -------------------

    select length('DOCU0006') from dual

    --> 8

    Get Substring and extract sequence
    -----------------------------------------

    select substr('DOCU0006', 5) from dual ( (8-4) + 1 )

    --> '0006'

    String to Number and increase with 1
    ---------------------------------------------

    select to_number('0006') +1 from dual


    Convert number back to string
    -----------------------------------

    Select to_char(7,'0999') from dual

    ---> '0007'


    ----------------------------------SQL Statements ------------------------------


    I would than add the '0007' to the prefix and write that as the ITEM_ID to the database.

    Anyone got a suggestion on how to to this in PL/SQL ?

    Regards,

    Njitter

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help with Statement (PL/SQL)

    My suggestion would be not to do it at all - just use a sequence period.

    But anyway, here is a PL/SQL function that does it:

    Code:
    create or replace
    function next_id( p_prefix in varchar2 ) return varchar2
    is
      v_item_id items.item_id%type;
    begin
      select p_prefix || ltrim(to_char(to_number(nvl(substr(max(item_id),-4),0)+1),'0000'))
      into   v_item_id
      from   items
      where  item_id like p_prefix||'%';
    
      return v_item_id;
    end;
    /
    You don't need to find the length of the id, because substr(x,-4) returns the LAST 4 characters.

    Some points to note:
    - the function is not guaranteed to return a unique value: if 2 users call it at around the same time for same prefix, they will both get same value returned. To prevent this you would have to take out a lock on the parent (lookup) table row for that prefix.
    - if max value was DOCU9999, then it will either raise an error or return 'DOCU#####' (depending on size of column).

  3. #3
    Join Date
    Jul 2003
    Posts
    3

    Re: Help with Statement (PL/SQL)

    Are you trying to help this person? Does this person know PL/SQL? Are you trying to spoon feed this person? Hmm..... This sounds way too familiar.


    Originally posted by andrewst
    My suggestion would be not to do it at all - just use a sequence period.

    But anyway, here is a PL/SQL function that does it:

    Code:
    create or replace
    function next_id( p_prefix in varchar2 ) return varchar2
    is
      v_item_id items.item_id%type;
    begin
      select p_prefix || ltrim(to_char(to_number(nvl(substr(max(item_id),-4),0)+1),'0000'))
      into   v_item_id
      from   items
      where  item_id like p_prefix||'%';
    
      return v_item_id;
    end;
    /
    You don't need to find the length of the id, because substr(x,-4) returns the LAST 4 characters.

    Some points to note:
    - the function is not guaranteed to return a unique value: if 2 users call it at around the same time for same prefix, they will both get same value returned. To prevent this you would have to take out a lock on the parent (lookup) table row for that prefix.
    - if max value was DOCU9999, then it will either raise an error or return 'DOCU#####' (depending on size of column).

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help with Statement (PL/SQL)

    Originally posted by djayaraj
    Are you trying to help this person? Does this person know PL/SQL? Are you trying to spoon feed this person? Hmm..... This sounds way too familiar.
    Really? If you look at the original post here, you can see that this person had made some effort to solve this for themselves, but had hit a wall.

    OTOH your recent post said, more or less: here is my homework. I'm not even going to try this myself, please just give me the answer.

    I'm quite prepared to write a bit of code to show someone how to use a particular technique. I am less inclined to do so when they clearly don't intend to engage their own brain.

Posting Permissions

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