Use of rownum in a view

Trying to create a view which consistent of :

1. Data from a set of tables, implemented as a
separat VIEW VIEW1 with an unique key.
CREATE OR REPLACE VIEW1 AS
SELECT ukey,... FROM divtables;

2. An subtable refering to the unique key, may contain a number of occurences for each key. Now implemented as VIEW2 containing rownum.
CREATE OR REPLACE VIEW2 AS
SELECT ukey,ROWNUM rno, ... FROM subtab;

ROWNUM - How to get around this
When using SQL from VIEW2 for a specific key, then rownum is 1,2..., when using in VIEW2 then rownum seems to be numbered for the base table as such.

RESULTVIEW - How to implement
=============================
The RESULTVIEW should be used in an generic application, where one datarecord should be present for each unique key (used for "mailmerge").
Assumes that subtable only contains max 4 rows. Was planning to implements it as:
CREATE OR REPLACE RESULTVIEW AS
SELECT ukey,...,
sum(decode(nvl(v2.rno,0),1,v2.val,0)) Val1,
max(decode(nvl(v2.rno,0),1,v2.desc,0)) Desc1,
....
sum(decode(nvl(v2.rno,0),4,v2.val,0)) Val4
max(decode(nvl(v2.rno,0),4,v2.desc,0)) Desc4,
FROM VIEW2 v2,VIEW1 v1
WHERE v2.ukey(+)=v1.ukey
GROUP BY ukey,...
;

The problem with rownum in VIEW2 makes the planned implementation impossible, any suggestions on how to get around this would be appreciated.

The result must be a view which contains only one record for each unique key, and may contains up to
four groups of values for subtable (5 values in each group).

Rownum will iterate over the whole table rather than just within each "ukey". You could use a ranking function, if your database version supports them:

CREATE OR REPLACE VIEW2 AS
SELECT ukey,ROW_NUMBER() OVER (PARTITION BY ukey) rno, ... FROM subtab;

Quote:

---

Originally Posted by stein.andersen

Trying to create a view which consistent of :
CREATE OR REPLACE RESULTVIEW AS
SELECT ukey,...,
sum(decode(nvl(v2.rno,0),1,v2.val,0)) Val1,
max(decode(nvl(v2.rno,0),1,v2.desc,0)) Desc1,
....
sum(decode(nvl(v2.rno,0),4,v2.val,0)) Val4
max(decode(nvl(v2.rno,0),4,v2.desc,0)) Desc4,
FROM VIEW2 v2,VIEW1 v1
WHERE v2.ukey(+)=v1.ukey
GROUP BY ukey,...
;

---

How does a ronumber (rno) become zero?
I see a flaw in your application desion.
Do not use views. It will slow down the performance.

Tamil