Using RANDOM function with dynamic range

Database

Using RANDOM function with dynamic range

Hello,

 

I am receiving the following error when I try to use a column name in the RANDOM function.

SELECT Failed. 3707:  Syntax error, expected something like an integer or a decimal number or a floating point number or '+' or '-' between ',' and the word 'max_row_num'. 

 

SELECT RANDOM(1,max_row_num)
FROM temptable;

 

How can I use the RANDOM function with an dynamic upper bound range?


Accepted Solutions
Junior Contributor

Re: Using RANDOM function with dynamic range

Try increasing the range of values and then apply modulo:

RANDOM(1,2147483647) MOD max_row_num +1

What's the actual usage of this?

 

1 ACCEPTED SOLUTION
6 REPLIES
Senior Apprentice

Re: Using RANDOM function with dynamic range

Hi,

You can't directly, the RANDOM function requires two integer literals. You can't use column names etc.

The best you can do is dynamically generate the sql (perhaps using an SP?) and then run the generated code.

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Using RANDOM function with dynamic range

According to the documentation, you can only specify constant integers.  To make one (or both) of the parameters dynamic then, you could use dynamic SQL in a stored procedure, or you could define a variable in a shell script or Windows cmd running bteq, or you could write your own UDF in C or Java - the algorithm is defined in the documentation.

Junior Contributor

Re: Using RANDOM function with dynamic range

Try increasing the range of values and then apply modulo:

RANDOM(1,2147483647) MOD max_row_num +1

What's the actual usage of this?

 

Re: Using RANDOM function with dynamic range

That worked perfect. The use case is that we can have 2 to many treatments for the same patient, that occur within the same day at multiple facilities, but the actual time isn't stored in the database or we would have chosen the first occurrence by date/time. So we need to pick one of the locations, which we were using the max(facility id). I wanted something more random, rather than selecting the event which corresponds to the highest numeric facility id. So I was going to assign each treatment a row number ID (using OLAP function), and then use the RANDOM function to pick one of the treatments, based on the dynamic upper bounded value equaling the max(row_number).

Junior Contributor

Re: Using RANDOM function with dynamic range

When you already use a ROW_NUMBER you can simply order by random like this:

SELECT *
FROM 
( -- must be nested, as RANDOM can't be used directly in a Windowed Aggregate
SELECT t.*, Random(1,1000000) AS rnd
FROM mytable AS t
) AS dt QUALIFY Row_Number() Over (PARTITION BY whatever ORDER BY rnd) = 1

Re: Using RANDOM function with dynamic range

Actually - that actually works for another use case I have as well. If I had x number of treatments and I wanted to select a random sample of them, your query would work quite nicely for that too.Thank you very much!!