inserting sequnce numbers in view

[LKBrwn_DBA]

Saritha,

What you meen is that even thogh the rows do not exist in TABLE1, you still need to
generate the same payin_no for rows in view1 for that specific date?
If this is not the case, then to me it still is somewhat confusing.
Could you paste the two SQL source for the views?

If this is the case, you can generate the payin_no from a function as follows:

create or replace function f_payin_no
(P_REv_acct_code VARCHAR2, P_pdate DATE)
return number is
r_no number(9):=0;
begin
select max(payin_no) into r_no
from table1
where table1.REv_acct_code = P_REv_acct_code
and table1.pdate = P_pdate;
Return r_no;
end;

Then in your view:

create or replace view2 as
select view1.REv_acct_code, view1.pdate, amt, prefix
, NVL(TABLE1.Payin_no, f_payin_no(view1.REv_acct_code, view1.pdate)
, cr_deb
from view1, table1
/


Thanks,
MikeOM>

[Ao]

Use of analitical function's

CREATE VIEW test_ao AS
SELECT A.* , dense_rank() over(ORDER BY job ) seq_number FROM EMP A