-
Sequence in Decode
Hi all,
I am using a sequence (from 001 to 999) in Decode as follows.
The Idea is to concatinate the number with 001 or 010. so that the generated number will like TSA001,TSA002.....TS100...TSA999.
The maximum numbe will be 999.
select decode
(length(Emp_Num_SEQ.Nextval),1,'TAS00'||Emp_Num_SEQ.Nextval,2,'TAS0'||Emp_Num_SEQ.Nextval,3,'TAS'||E mp_Num_SEQ.Nextval) from dual
Coz i select once to findout the Lenght and once again to concatinate the value to TAS, i am using the same sequence.
Logically speaking there should be jump of sequences.
But i am getting correct values.
How is this ? Can this create any problems in future ?
-
All the occurence of Emp_Num_SEQ.Nextval will be substituted with its value once per row fetch..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Re: Sequence in Decode
Originally posted by bang_dba
select decode
(length(Emp_Num_SEQ.Nextval),1,'TAS00'||Emp_Num_SEQ.Nextval,2,'TAS0'||Emp_Num_SEQ.Nextval,3,'TAS'||E mp_Num_SEQ.Nextval) from dual
There is no need to use DECODE at all. It would be much simplier if you code it like this:
Code:
select 'TAS'|| ltrim(to_char(Emp_Num_SEQ.Nextval,'099')) from dual;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Whenevr I see sequences being concatanated to a text string it brings up bad memories of previous projects. Personally I'd rather not see something like that happen - it leads to tears down the road.
-
Re: Re: Sequence in Decode
Originally posted by jmodic
There is no need to use DECODE at all. It would be much simplier if you code it like this:
Code:
select 'TAS'|| ltrim(to_char(Emp_Num_SEQ.Nextval,'099')) from dual;
Thanx Jmodic. U r really an Oracle GURU
-
Hi,
Apart from rollback and Cache problem( i am not using cache option),
is there any other reason due to which sequence nubers will jump ?
-
If nocache option is used for sequence, you will not see gap in the sequence.
Did you see it?
When the sequence is created with cache option, you may see gap in it, only when the shared pool flushed or shutdown abort was used.
Tamil
-
Originally posted by tamilselvan
If nocache option is used for sequence, you will not see gap in the sequence.
Tamil
What will happen when after sequence is generated and rollback is issued? Gaps are generated, no?
-- Dilip
-
Thanx Tamil
Originally posted by patel_dil
What will happen when after sequence is generated and rollback is issued? Gaps are generated, no?
Yes, For rollback, the sequence number is skipped.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|