inserting sequnce numbers in view
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: inserting sequnce numbers in view

  1. #1
    Join Date
    Jun 2001
    Posts
    109
    Hello all,
    I am creating a view which has columns that come from tables and I also need to create a new column in the view which does not belong to any of those tables but that new column will have values comming from sequence.

    IS this possible ,if so can you please let me know how.

    thanks
    Saritha

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Red face

    Yes,

    Just select the sequence and you can give it an alias name like:

    select myseq.nextval the_seq, col1, col2, ... from table;

    MikeOM>

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Talking

    Ooops sorry, dislexia today.
    Did not realize you asked 'IN A VIEW'.
    So the respose is NO. (unless someone has a workaround like hiding the sequence within a function?).
    MikeOM>

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool

    Thats it!

    create or replace function fseq
    return number
    is
    ret number;
    begin
    select myseq.nextval into ret from dual;
    return ret;
    end;
    /

    then:

    create view the_view as
    select fseq, col1, col2, ...
    /
    and it works!
    MikeOM>

  5. #5
    Join Date
    Jun 2001
    Posts
    109
    THANKS, WHEN DOES THAT FUNCTION GETS EXECUTED? i THINK IT GETS EXECUTED WHEN YOU RUN THAT VIEW.


  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459
    Yes, It gets executed EVERY time you execute the view!

    Also, it will CHANGE the sequence number for the SAME row each time it is executed because the sequence increaments each time because of nextval!!!.

    To generate a sequence from 1 to n each time you execute the view, use 'rownum' as in:

    create view the_view
    as
    select rownum seq_nbr, col1, col2, col3 from ....
    /
    Cheers,
    MikeOM>

  7. #7
    Join Date
    Jun 2001
    Posts
    109
    Thanks, it works fine but I don't tto increment my sequnce for each row. I have column called TC_tran_date which is present in that same view which come from a table, I wnat my sequence to increment only once for each date.
    For eg: today(31-jul-02) the payin_no should be same for all the records that are entered today. That payin number should increment only once for each day or date.

    Can you please let me know

    thx
    saritha

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Question

    Saritha,

    Your problem seems to be more complex than just creating a view using a sequence!
    In order to understand better, are you using this view only to 'select' rows from those tables?
    Or are you using this view to 'update' or 'insert' rows in a table trying to generate the next sequence
    number for the payin column?
    Can you post an example of the results you expect and provide some more specifications?

    Cheers,
    MikeOM>

  9. #9
    Join Date
    Jun 2001
    Posts
    109
    Yes you are right this is a complicated thing. Well, I am nit trying to update/insert any table all I am doing select from that view.
    Let me explain it clearly.

    tableA + tableB + tableC = view1;
    view1 + table1 = view2;

    view1 has a column called totamtpd and table1 has a column called payin_amt.
    view2 will have a column called amount which is subtracted amount from view1 and table1.
    ie. view1.totamtpd - table1. payin_amt =view2.amount.
    this amount subtraction should occur only when there is amatching records in the view1 and table1 else view2 should have the amount from view1.
    Payin_no field is present only in table1, so view2 will also have the Payin_no only for those matching records which are comming form view1 and table1.
    I need payin_no generated for each record in view2 even though there is no matching records.
    The view2 gets the amount even though there is no matching records but it doesn't get payin_no for non matching records because there is no Payin_no column in view1.


  10. #10
    Join Date
    Jun 2001
    Posts
    109
    REv_acct_code pdate amt prefix Payin_no(62) cr/deb
    -----------------------------------------------------------
    10000 2430 22111 26-APR-02 $100 28 62 D
    10000 2430 27136 26-APR-02 $110 28 D (no payin_No)
    53000 26-APR-02 $110 28 D (no_payin_no)

    I should have 62 as payin_no for those two rows too.

    the first records got the payin_no (62) because there was a matching records in view1 and table1 and 100$ amount for that row is subtracted amount for matching rev_acct_code.
    The second and 3rd record don't have payin_no because they are coming from view1 and no matching records found in table1.
    I used decode function but still its not working.

Posting Permissions

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



Click Here to Expand Forum to Full Width