[njitter]

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

[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:

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).

[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.

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:

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).

[andrewst]

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.