CODEWITHSUNDEEP
oracle-database
Senthilnathan
Senthilnathan

Reputation: 1267

Sequence with variable

In SQL we will be having a sequence. But it should be appended to a variable like this

M1,M2,M3,M4....

Any way of doing this ?

Upvotes: 1

Views: 552

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

Consider having the prefix stored in a separate column in the table, e.g.:

CREATE TABLE mytable (
   idprefix VARCHAR2(1) NOT NULL,
   id       NUMBER NOT NULL,
   CONSTRAINT mypk PRIMARY KEY (idprefix, id)
);

In the application, or in a view, you can concatenate the values together. Or, in 11g you can create a virtual column that concatenates them.

I give it 99% odds that someone will say "we want to search for ID 12345 regardless of the prefix" and this design means you can have a nice index lookup instead of a "LIKE '%12345'".

Upvotes: 9

Thilo
Thilo

Reputation: 262534

select 'M' || my_sequence.nextval from dual;

Upvotes: 4

Related Questions