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'.
How can I use the RANDOM function with an dynamic upper bound range?
Solved! Go to Solution.
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.
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.
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).
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
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!!