This content has been marked as final. Show 8 replies
Rows in a table are like balls in a basket.
After I pull a Red ball out of the basket, which ball is "next"?
Hi and welcome to the forums!
Can you give us some more information about how you would use this next ID? What is your Oracle version (SELECT * FROM V$VERSION)?
My guess is that you may be trying to create GAPLESS IDs which is a futile effort. Most likely your approach won't work in a multi-user environment unless you serialize access which could have a huge impact on performance.
I'm using 10g.
The ID's aren't primary keys or indexes; I have a separate ID column for that. I have a standard html form that does a submit, but I need it to submit to the next form_id that the user has access to
<form action="mypage?form_id=next_oracle_formid" >
can you use SEQUENCE?
I have no idea, I don't know Oracle that well nor have I ever used SEQUENCE before.
What is the structure of your table and what makes an id 'next'. Is it the next higher ID (and maybe some other properties)?
I guess you are having FORM_ID with some gaps. So after 2 the next form Id might be 5.
3 and 4 are missing.
If that is the case then use the LEAD function.
WITH t AS (SELECT 1 form_id FROM DUAL UNION ALL SELECT 2 form_id FROM DUAL UNION ALL SELECT 5 form_id FROM DUAL UNION ALL SELECT 6 form_id FROM DUAL UNION ALL SELECT 7 form_id FROM DUAL) SELECT form_id, LEAD (form_id) OVER (ORDER BY form_id ASC) l_form_id FROM t;
Edited by: drom on Dec 3, 2012 12:04 AM