Forum Stats

  • 3,872,269 Users
  • 2,266,406 Discussions
  • 7,911,115 Comments

Discussions

Generate Sequence Value

Jesun Ahmad Ushno
Jesun Ahmad Ushno Member Posts: 7 Green Ribbon

Greetings Seniors & Experts, Hope you all are doing great. So, recently I've been practicing SQL.

This is the challenge that I'm facing currently. How can I code to generate the sequence automatically in SQL Command? Let's say I want to create a table named STUDENT, and I want to maintain the id which will be followed sequences 01, 02, 03 ... up to this point the code is,

CREATE TABLE TEST

(

 ID NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 CYCLE ORDER KEEP)

);

but what shall I need to for the STU_ID which I want it to follow the sequence like ID but additionally it will add 'STU_ID_00' ?


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,720 Red Diamond

    Hi, @Jesun Ahmad Ushno

    So id is a virtual column; it's always derived, never explicitly entered. You want stu_id to be always derived, never explicitly entered, so make it a virtual column, too. Here's one way:

    CREATE TABLE test
    ( id 	   NUMBER GENERATED ALWAYS AS IDENTITY  ( START WITH  1
      	   	  	   	   		  INCREMENT BY 1
    						  MINVALUE 	 1
    						  MAXVALUE   99
    						  CACHE 	20
    						  CYCLE ORDER 	KEEP
    						 )
     , stu_id  VARCHAR2 (10)  GENERATED ALWAYS AS ('STU_ID_' || TO_CHAR (id, 'FM00'))
    ...
    

    Do you really need a redundant column like stu_id?

    Jesun Ahmad Ushno

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,720 Red Diamond

    Hi, @Jesun Ahmad Ushno

    So id is a virtual column; it's always derived, never explicitly entered. You want stu_id to be always derived, never explicitly entered, so make it a virtual column, too. Here's one way:

    CREATE TABLE test
    ( id 	   NUMBER GENERATED ALWAYS AS IDENTITY  ( START WITH  1
      	   	  	   	   		  INCREMENT BY 1
    						  MINVALUE 	 1
    						  MAXVALUE   99
    						  CACHE 	20
    						  CYCLE ORDER 	KEEP
    						 )
     , stu_id  VARCHAR2 (10)  GENERATED ALWAYS AS ('STU_ID_' || TO_CHAR (id, 'FM00'))
    ...
    

    Do you really need a redundant column like stu_id?

    Jesun Ahmad Ushno
  • Jesun Ahmad Ushno
    Jesun Ahmad Ushno Member Posts: 7 Green Ribbon

    Hello @Frank Kulash,

    Thanks for your response. It was a huge help. 👍 ⭐⭐⭐⭐

    stu_id column is not necessary in some cases it is needed.

    What about if I want to maintain it like "xx-xxxx-x" ?

    ex: 18-36632-1, 10-11111-1, 10-11111-2 etc,

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,720 Red Diamond

    Hi, @Jesun Ahmad Ushno

    What about if I want to maintain it like "xx-xxxx-x" ?

    ex: 18-36632-1, 10-11111-1, 10-11111-2 etc,

    Sorry, I don't understand what you're asking. Explain, in general terms, what you want. Giving specific example is great, but always give the general rules, too.

  • Jesun Ahmad Ushno
    Jesun Ahmad Ushno Member Posts: 7 Green Ribbon

    Greetings @Frank Kulash, Hope you are doing great. Here is the problem I'm trying to explain to you. Hope you might get catch the idea.


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,720 Red Diamond

    HI, @Jesun Ahmad Ushno

    Sorry, I don't understand what a "random/sequenced" value is. If values are random, that means you can't predict what the next value will be by looking at the previous values, and if values are sequenced, then you have an excellent chance of predicting what the next value will be by looking at the previous values.

    If

    • year_admitted is a 4-dight NUMBER (e.g. 2018),
    • seq is a NUMBER with anywhere from 1 to 7 digits (e.g. 36632), and
    • session is a 1-digt NUMBER (e.g. 1)

    then

    TO_CHAR (MOD (year_admitted, 100), 'FM00')      || '-' ||
    TO_CHAR (seq,                      'FM0000000') || '-' ||
    TO_CHAR (session,                  'FM0')
    

    returns a 12-character string like '18-36632-1'. Keep year_admitted, seq and session in separate columns. You can have a virtual column for the 12-character string, if you want.

    Jesun Ahmad Ushno