OraFAQ Forum: SQL & PL/SQL » Sequence question, pls help

Home » SQL & PL/SQL » SQL & PL/SQL » Sequence question, pls help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sequence question, pls help [message #41257]

Thu, 12 December 2002 06:50

Vinod
Messages: 76
Registered: April 1999

Member

I want to generate a sequence like S01,S02,S03....S10,S11...

How do i do this, pls help me

Thanks

Vinod

Report message to a moderator

Re: Sequence question, pls help [message #41260 is a reply to message #41257]

Thu, 12 December 2002 07:01

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

startwith and incrementby values in sequence are NUMBERs.
so you cannot have characters in them.
a workaround would be like this

SQL> ed
Wrote file afiedt.buf

  1  create sequence seq123
  2  start with 1
  3* increment by 1
SQL> /

Sequence created.

SQL> select 'S'||seq123.nextval from dual;

'S'||SEQ123.NEXTVAL
-----------------------------------------
S1

SQL> /

'S'||SEQ123.NEXTVAL
-----------------------------------------
S2

SQL> /

'S'||SEQ123.NEXTVAL
-----------------------------------------
S3

Report message to a moderator

Re: Sequence question, pls help [message #41265 is a reply to message #41257]

Thu, 12 December 2002 09:29

casanova
Messages: 4
Registered: December 2002

Junior Member

Use this:

select 'S'||decode(length(seq123.nextval), 1, 0||seq123.currval, seq123.currval) from dual;

Report message to a moderator

Re: Sequence question, pls help [message #41271 is a reply to message #41257]

Thu, 12 December 2002 11:08

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

What happens after 99?

The expression is just:

select 'S' || lpad(seq.nextval, 2, '0') from dual;

select 'S' || to_char(seq.nextval, 'FM00') from dual;

Report message to a moderator

Previous Topic:	sql problem
Next Topic:	Pls help me its urgent

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 17:31:33 CDT 2024