Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    London
    Posts
    5

    Unanswered: select emp_num from emp SAMPLE(20);

    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

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    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
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  3. #3
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    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
    nn

  4. #4
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    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
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  5. #5
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118
    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
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

Posting Permissions

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