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:
Query with random search is possible ??
- dr_gori
- 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
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
Example:
made! : Shock:
WHERE ROWNUM<=150
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>
-
- 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?
-
- 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!!!
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!
-
- 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:
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 *
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
-
- Moderador
- Posts: 1396
- Joined: Fri, 01 Feb 2008 2:06 pm
- Location: Rio de Janeiro - RJ
- Contact:
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.
-
- Information
-
Who is online
Users browsing this forum: No registered users and 15 guests