| Alpha Numeric Sequence Updating Problem. [message #554889] |
Fri, 18 May 2012 06:59  |
 |
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
I have a Typical Issue;
we have document serial Nos increment program,
which has the below query for selecting and Updating, which generates a
Numeric Value;
Select Query:
Select doc_slno
from fin_jv_slno_mas
where jv_month = '05' and
jv_year = '2012'
Output of this Query: 800001
Update Query:
update fin_jv_slno_mas set doc_slno=docno+1 where
jv_year = '2012' and
jv_month = '05'
Output after Updation: 800002.
The above query is a normal Sequence updating for a Numeric Value;
now we need to update it as Alpha Numeric as below
Example: 8A0001
How can we give the above update statement for Alpha Numeric updation?
Thanks in Advance,
Stalin.
[EDITED by LF: removed superfluous empty lines]
[Updated on: Fri, 18 May 2012 14:43] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Alpha Numeric Sequence Updating Problem. [message #554915 is a reply to message #554911] |
Sat, 19 May 2012 03:55  |
|
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
Hi brayampettai,
we cannot replace A to B when getting Maximum.
Actually the sequence will go like;
8A0001
8A0002
8A0003
8A0004
8A0005
8A0006, etc.,
This is the below coding i tried to update as per Cookiemonster;
update fin_jv_slno_mas set doc_slno= SUBSTR(doc_slno,1,2) || ltrim(TO_CHAR(TO_NUMBER(SUBSTR(doc_slno,3,4)+1),'0000') )
where jv_year = '2012'
and jv_month = '05'
but the above coding works(updating) when we give it in a SQL Developer or any other Front End tool, but when we put the above update statement in a forms 'Procedure_Body' its not working, so the thing i find out is, this update statement works in a Sql Developer not in a Forms Builder, what might be the Cause?
Please reply,
Stalin.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
