Query with random search is possible ??

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
jctq
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 27
Joined: Wed, 08 Feb 2006 1:21 pm
Location: São Gonçalo
Att,
Julio Quintão

People need to make a random query, who bring records existing in the table but randomly, there's someone there already did it. If someone knew and can help me I thank you very much.

Valeu ...

JCTQ: Idea:
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Take a look at this post: http://en.glufke.net/oracle/viewtopic.php?t=243
: -O
jctq
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 27
Joined: Wed, 08 Feb 2006 1:21 pm
Location: São Gonçalo
Att,
Julio Quintão

Thanks for Gori !!!

But what I really wanted, is to make a query of a table that I have 1000 records and select only 150 Aleastily, I have how to do this ??

Thank you very much,
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Yes, put in your search a

Select all

WHERE ROWNUM<=150
Example:

Select all

SQL> SELECT * 
  2  FROM  
  3    (SELECT * 
  4     FROM EMP 
  5     ORDER BY DBMS_UTILITY.GET_HASH_VALUE(TO_CHAR(dbms_utility.get_time)||ename,2,1048576) 
  6    ) 
  7  WHERE ROWNUM <=3 
  8  / 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
 
SQL> / 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
 
SQL> / 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
 
SQL> / 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO 
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
 
SQL>  
made! : Shock:
jctq
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 27
Joined: Wed, 08 Feb 2006 1:21 pm
Location: São Gonçalo
Att,
Julio Quintão

Ai Gori, thanks even expensive ....
This portal is very good ....

Hugs ...
: LOL:
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

I need to generate randomly from a client table that has 1 million records and I just want to generate the number of 50,000 customers ... how can I do this? I have questions about how to use dbms_utility.get_hash_value to make this query .. can anyone help me?
RodrigoValentim
Moderador
Moderador
Posts: 367
Joined: Tue, 25 Mar 2008 3:41 pm
Location: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Thomas posted an example of one studied in it and you get the easy solution!
mariogus
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 157
Joined: Fri, 30 Nov 2007 1:17 pm
Location: Porto Alegre - RS

Personnel - follows below the SQL:

But what happens is that he gets lost in the internal WHERE clause and brings the other data ... too .. What can be done wrong?

(DBMS_UTILITY.GET_TIME) || DIMCLI_ID, 3,429604886) - I have doubt about these last bold parameters in bold.

SQL:

Select all

SELECT *  
FROM  
( 
SELECT *  
FROM dim_cliente_suat d 
where d.tipo_conta = 'D' 
and d.perfil_cobranca_conta = 'PAG' 
and d.brand = 'terra' 
and d.dt_cadastro_conta < sysdate - 90  
and (d.status_conta = 'CA' and d.dt_status_conta between sysdate -240 and sysdate -90)  
or (d.status_conta = 'AT') 
ORDER BY DBMS_UTILITY.GET_HASH_VALUE(TO_CHAR(dbms_utility.get_time)||dimcli_id,3,429604886)  
)  
WHERE ROWNUM <=50000
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

GET_HASH_VALUE FUNCTION

This function Computs to hash value for the given string.
Syntax


Parameters
Table 104-20 GET_HASH_VALUE FUNCTION Parameters
Parameter Description
Name - String to Be Hashed.
Base - Base Value for The Returned Hash Value to Start at.
hash_size - Desired Size of the hash table.
esttevan
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Wed, 07 Oct 2009 10:11 am
Location: Nova Hartz - RS

I used dbms_ndon.value on Order by

Select all

Select * From ( 
Select 
	* 
From	 
emp 
Order By Dbms_Random.Value) a 
Where 
Rownum < 3
Estevan.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests