Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Apr 2011
    Posts
    40

    Question Unanswered: Creating sequence ??

    Hi all,

    I was trying to create a sequence like described below.

    Every member has a unique member number which starts with the letter 'M' followed by three digits (Mxxx)
    I know how to create a sequence with only numbers but I don't have any idea how to start with a character(M).

    Can anybody help me with this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1* select 'M'||ltrim(to_char(3,'0009')) from dual
    SQL> /
    
    'M'||L
    ------
    M0003
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    Code:
      1* select 'M'||ltrim(to_char(3,'0009')) from dual
    SQL> /
    
    'M'||L
    ------
    M0003
    I didn't get this

    i can craeate SEQUENCE like below

    CREATE SEQUENCE member_number_seq
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 999
    NOCACHE
    NOCYCLE;

    but how can i insert a char(M) infront of every number created by member_number_seq (M001, M002....M999)?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    select 'M'||to_char(member_number_seq.nextval, '0009') from dual

  5. #5
    Join Date
    Apr 2011
    Posts
    40
    Ok, i made it easy for myself.

    created like this
    CREATE SEQUENCE member_number_seq
    INCREMENT BY 001
    START WITH 1
    MAXVALUE 999
    NOCACHE
    NOCYCLE;
    and inserted like below
    INSERT INTO members
    (code)
    VALUES ( ‘H’ || member_number_seq.NEXTVAL);
    but I couldn't get the result i wanted.

    i want like H001, H002, H003....H099...H999

    but i am getting numbers like H1, H2, H3....

    any help with this?
    Last edited by arayici; 04-24-11 at 08:46.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Because you did not use the to_char() function as show in anacedent's and my example.
    Why did you leave it out?

  7. #7
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by shammat View Post
    Because you did not use the to_char() function as show in anacedent's and my example.
    Why did you leave it out?
    Ok I get it

    INSERT INTO members
    (code)
    VALUES ( ‘H’ || to_char (member_seq.NEXTVAL, '009');
    created ike H 001, H 002, H 009...

    but needed to increase my "code NUMBER(4") to "code NUMBER(5)"?
    is there anyway i can do with only "code NUMBER(4)"
    As this is for a coursework?

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You cannot use number at all to store that value, as H001 is an invalid numeric value. You have to use VARCHAR for the code column.

  9. #9
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by shammat View Post
    You cannot use number at all to store that value, as H001 is an invalid numeric value. You have to use VARCHAR for the code column.
    SORRY i made a mistake earlier

    I am using "code VARCHAR2(5)".

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    am using "code VARCHAR2(5)"
    is there anyway i can do with only "code NUMBER(4)"
    So your question is actually: "can I store 5 characters in a column defined as VARCHAR(4)?"

    The answer is a clear and obvious: NO

  11. #11
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by shammat View Post
    So your question is actually: "can I store 5 characters in a column defined as VARCHAR(4)?"

    The answer is a clear and obvious: NO
    No! actually my question is

    I only need 4 characters (Mxxx) but i need to use VARCHAR2(5) as it is leaving gap between "M" and the numbers like "M 001"?

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    my example had no gap
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by anacedent View Post
    Code:
      1* select 'M'||ltrim(to_char(3,'0009')) from dual
    SQL> /
    
    'M'||L
    ------
    M0003
    I couldn't get this one worked.

    prob I was doing wrong....
    INSERT INTO members
    (code)
    VALUES ( ‘H’ || ltrim (to_char (member_seq.NEXTVAL, 3,'0009'));

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I couldn't get this one worked.

    my car won't go.
    tell me how to make my car go.

    OK, We both agree that you have a problem.
    If/when you decide to post some useful details (like actual error), your problem may get solved.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by arayici View Post
    I couldn't get this one worked.
    to_char() only takes two arguments, you provide three.

    Code:
    INSERT INTO members
    (code)
    VALUES ( 'H' || ltrim (to_char (member_seq.NEXTVAL,'0009'));
    You should really learn how to read the manual, it's all documented there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •