-
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;
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|