View creation with record limit

Asked

Viewed 123 times

-1

I want to create a view that should only return a maximum of X records, independent of the selection clauses applied. That is, whatever filter you apply, the number of lines should always be X. (with the ROWNUM <= X it does not seem to work because then the result of the records of the clause "Where" will be lower than X because the data will be restricted first to the universe under the ROWNUM <= X).

oracle view

  • And if the filter is restrictive to the point of not returning any record?

  • Records are selected by filter constraints and after that returns the number of records specified by the ROWNUM condition, if there are sufficient records.

  • If you select from the outside ?

  • Select on the outside didn’t work. I want to always return such X records and with ROWNUM in the creation of the view and with the filter in select, the records that return, are less than those that actually exist.

  • Practical example: I have a table whose one of the attributes is the NAME of the employees; this NAME is part of the view I created; any query I make to that view should return at most 10 records; passing the solution through ROWNUM < 10 and executing a select restricting the name to "Pedros", the result will be the " Pedros" that exist in the first 10 records, but I wanted to return 10 "Pedros" (and in the table there are many more).

  • CREATE OR REPLACE V_VIEW AS SELECT COLUNA1,COLUNA2... FROM (SELECT COLUNA1,COLUNA2 FROM TABLE ORDER BY COLUNAN) WHERE ROWNUM < 1000

  • The problem is that it is not possible to use "order by". Restricting another column other than "COLUNAN" to the query does not solve my problem.

Show 2 more comments

1 answer

0

Using Analytic Functions , creates a rank and is ordered by it , the view goes on top of this.

    SELECT primeironome, Ident, Nome, Dt.Nasc, Local
FROM
(
SELECT a.ID AS Ident, 
       B.NOME AS Nome, 
       SUBSTR(TRIM(NOME),1,INSTR(TRIM(NOME),' ')) primeironome,
       TO_CHAR(DATNASC,'YYYY-MM-DD') AS Dt.Nasc, 
       LOCRESID AS Local,
       dbms_randam.value( vl_alea
       RANK() OVER (PARTITION BY primeironome  ORDER BY vl_alea)  rank 
FROM IDPESSOAL
) WHERE RANK < 11

  • I cannot apply your solution. If it were possible to be more specific, I would appreciate it. Um caso prático: tabelas "TABIDENT" e "TABBIOG"; view CREATE OR REPLACE FORCE VIEW IDPESSOAL AS SELECT a.ID AS Ident, B.NOME AS Nome, TO_CHAR(DATNASC,'YYYY-MM-DD') AS Dt.Nasc,&#xA; LOCRESID AS Local FROM TABIDENT a,TABBIOG b&#xA;WHERE a.ID = b.ID; The idea is that whatever the query to the "IDPERSONAL" view returns only 10 records. For example: select * FROM IDPERSONAL Where NAME like 'A%'; select * FROM IDPERSONAL Where LOCRESID like 'A%'; select * FROM IDPERSONAL Where DATNASC > '1980-01-01';

  • "TABLE_NAME", "COLUMN_NAME" ?????!!!!! "FROM IDPERSONAL"I didn’t understand!!! And the "final client" can only query’s similarities to the examples I indicated "For example: select * FROM IDPERSONAL Where NAME like 'A%'...", etc and not the last approach you gave me.

  • Edited...................................

  • The value of "rank" always gives 1.

  • There should only be 1 in this grouping, I believe I have not understood how you want to group. What to say with "Pedros" ?

  • In my table "TABBIOG" there are for example 500 people with the name "Pedro"; if I do: select * from idpersonal Where name like 'Pedro%' I want to return only 10; for all other attributes that can be searchable (id, datnasc, local) the same has to happen (return only 10 records). The end-user can only query the "IDPERSONAL" view in this way "select ? from idpersonal Where ....."

  • I tested this solution (in another table) and it worked.

  • The "rank" already works but does not solve because almost always will be returned variety of filtered attributes. Thus, more than 10 records will still be returned (which we do not want to happen). Moreover, as I said earlier, it does not prevent the creation of several views and/or querys and if the example given is one of the querys available to the end user, nothing guarantees that it does not change it.

  • I put a Random , should work and generate random

Show 4 more comments

Browser other questions tagged oracle view

You are not signed in. Login or sign up in order to post.