[AJCG1976]

All,

I wonder if you can be some help, the above query returns a random 20% of the table emp.. I would like to beef this up to return a random 20000 rows (845000 in the table) from another table (clients) after I have already done a set of filters in the sql statement i.e.

select *
from le_clients a, le_flags b
where lc_client_id = lf_client_id
and LF_DECEASED_FLAG is null
and LF_MPS_FLAG is null
and LF_GONEAWAY is null
and lc_cust_flag is null
and LF_ET_FLAG is null
and LF_BEREAVE is null
and LF_SCR_OUT is null
and LF_CHIEF_EXEC is NULL
and LF_F2FSUPP_FLAG is null
and lf_utility_flag is null
and LF_STATUS = 'F2F'
and lf_catchment = 'IN_AREA'

Any help would be great....

Rgards

Adam

[remivisser]

Is this of any help?


SELECT *
FROM
(
select *
from le_clients a, le_flags b
where lc_client_id = lf_client_id
and LF_DECEASED_FLAG is null
and LF_MPS_FLAG is null
and LF_GONEAWAY is null
and lc_cust_flag is null
and LF_ET_FLAG is null
and LF_BEREAVE is null
and LF_SCR_OUT is null
and LF_CHIEF_EXEC is NULL
and LF_F2FSUPP_FLAG is null
and lf_utility_flag is null
and LF_STATUS = 'F2F'
and lf_catchment = 'IN_AREA'
)
SAMPLE (20)

Remi

[NoviceNo1]

Originally posted by remivisser
Is this of any help?


SELECT *
FROM
(
select *
from le_clients a, le_flags b
where lc_client_id = lf_client_id
and LF_DECEASED_FLAG is null
and LF_MPS_FLAG is null
and LF_GONEAWAY is null
and lc_cust_flag is null
and LF_ET_FLAG is null
and LF_BEREAVE is null
and LF_SCR_OUT is null
and LF_CHIEF_EXEC is NULL
and LF_F2FSUPP_FLAG is null
and lf_utility_flag is null
and LF_STATUS = 'F2F'
and lf_catchment = 'IN_AREA'
)
SAMPLE (20)

Remi

Hi Remi,
When i try this SQL that you gave, i am getting
"SQL command not properly ended" error (ORA-933)

This is the query that i am trying

SELECT *
FROM
(
select * from emp
)
SAMPLE (10)

What could be the reason for this ?
Thanks

[remivisser]

ooops, you're right (i didn't test the statemanet before I posted it...)

SQL> create table demo as select object_id from all_objects;

Table created.

SQL> select * from (select * from demo) sample (0.01);
select * from (select * from demo) sample (0.01)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select * from (select * from demo sample (0.01));

OBJECT_ID
----------
19193
19428

This is rather funny behaviour.

It looks like the sample clause has to be directly after the table(and not after a subquery...)

Remi

[remivisser]

From the manual:

You can specify SAMPLE only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE TABLE ... AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.

Hope this helps!

Remi