help with this simple proc?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Tue, 6 May 2003 08:58:24 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703A34A86@lnewton.leeds.lfs.co.uk>

Morning Gents,

>> Just generate the percentage by creating a fraction solving for that
number of records out of total
>> records in the table. A little trunc or round and you are in
business.

FYI : computing a fraction of the number of rows in the table and passing that to SELECT ... SAMPLE(n) does not work ! I know because I've been down this road myself.

I have an asset tracking system and I have to generate 10 randowm asset numbers each month for our finance departments hardware and software audit. I tried everyhting to get 10 rows out of the SELECT, but the final method is to SELECT from the SELECT with a WHERE ROWNUM < 11 clause.

You can try it for yourself using the SCOTT schema, try selecting 10% of the EMP rows, but do it a few times and see how many different numbers of rows you get back ! I've just tried it using a sample of 10% and got the following number of rows back :

1, 0, 5, 2, 1, 3, 2, 3

all from a 14 row table. I get similar results on my asset table which has 1,266 rows in it, the numbers are :

127, 122, 112, 110, 130, 141, 115, 123

HTH Regards,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Received on Tue May 06 2003 - 02:58:24 CDT