Using Sample Function.

Database

Using Sample Function.

Hi All,

Just wondering if this is possible in anyway...or if anyone has any other ideas to achieve what I want.

I have a table containing 10,000 rows, and everyday I want to take a Sample of X (X can change on a daily basis).

Ideally, I would like X to be a variable from a "variable" table I have setup (however this SQL is not permitted).

Eg.

Select
A.*
from
MYTABLE A
,VARIABLE V
Sample V.DAILY_SAMPLE
;

The Variable table has one field (DAILY_SAMPLE) which holds the sample amount that I want to select.

The main reason for this request is that the above code is in production, and can't be changed. I can however update the Variable table.

Thanks...

Luke
3 REPLIES
N/A

Re: Using Sample Function.

Hi Luke,
the only way i can think of is: Build the query string within a Stored Procedure and use Dynamic SQL (dbc.sysexecsql) to insert/select into a Global Temporary table (Or create a Volatile Table). After CALLing the SP you have to submit a SELECT * FROM temptable.

Dieter
Teradata Employee

Re: Using Sample Function.

If you are using Unix this would be very simple using the HEREDOC form. For some reason the web-site is striping out some characters in the example - so where you see the two > signs as in >>EOF replace with two < signs.

#!/usr/bin/ksh

rm $HOME/sample.dat

#replace the > with <
bteq>>EOF

.run $HOME/mylogon;

.export file=$HOME/sample.dat;

Select sample_size (title '') from VARIABLE;

.export reset

EOF

export SAMPLE_SIZE=$(cat $HOME/sample.dat)

#replace the > with <
bteq>>EOF

.run $HOME/mylogon;

Select
A.*
from
MYTABLE A
Sample $SAMPLE_SIZE;
;

EOF

echo "done"

Re: Using Sample Function.

Thanks guys...

Unfortunately we are running on a mainframe environment, and also running dynamic SQL isn't allowed!

I'll have to look into some way of passing variable through our scheduling system..

Luke.