Use of rownum in a view

[stein.andersen]

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).

[Scorby]

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;

[tamilselvan]

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