Use of rownum in a view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Use of rownum in a view

  1. #1
    Join Date
    Oct 2005
    Posts
    2

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

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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;

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width