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.
Just select the sequence and you can give it an alias name like:
select myseq.nextval the_seq, col1, col2, ... from table;
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?).
create or replace function fseq
select myseq.nextval into ret from dual;
create view the_view as
select fseq, col1, col2, ...
and it works!
THANKS, WHEN DOES THAT FUNCTION GETS EXECUTED? i THINK IT GETS EXECUTED WHEN YOU RUN THAT VIEW.
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
select rownum seq_nbr, col1, col2, col3 from ....
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
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?
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.
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.
Click Here to Expand Forum to Full Width