Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Thumbs up Unanswered: Need sequence for this logic

    Hi,

    table
    -------

    id code
    145623 xyz001
    178956 sbh002
    165895 sbi003

    As per this logic,for every new entity of id a new autogenerated code will be generated.
    This (xyz or sbh or sbi) code should be fetched using select query

    (select b_code into table.code from cmp where c_id =145623)

    using id(145623). And 001,002 is autogenerated code and should be concentrated with xyz ex: xyz001

    Here we can use trigger with sequence

    Could you please help me..

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    create a before trigger on insert on the table an a sequence. In the trigger append xyz to the beginning and

    :new.id := 'xyz'||to_char(my_seq.nextval);

    Now write the trigger and show us what you wrote
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, not really. Such a code would raise
    Code:
    PLS-00357: Table,View Or Sequence reference 'MY_SEQ.NEXTVAL' not allowed in this context
    Sequence number should be fetched within the SELECT statement.

    As of "001" part, LPAD function will do the job.

Posting Permissions

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