Teradata SQL – Generate Random Number Between Column Values

randomteradata

I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column?

The function RANDOM( lower_bound , upper_bound ) apparently is restricted to only accept fix number rather than field / column name.

--THIS ONE FAILS:
WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS (
SELECT 'A', 0, 3 FROM CTE UNION ALL
SELECT 'B', 1, 9 FROM CTE UNION ALL
SELECT 'C', 2, 5 FROM CTE UNION ALL
SELECT 'D', 1, 12 FROM CTE
), CTE(DUMMY) AS (SELECT 'X')
SELECT T.*,
RANDOM(T.VAL_MIN, T.VAL_MIN) --HERE'S THE PROBLEM
FROM TRIAL T;

--THIS ONE WORKS:
WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS (
SELECT 'A', 0, 3 FROM CTE UNION ALL
SELECT 'B', 1, 9 FROM CTE UNION ALL
SELECT 'C', 2, 5 FROM CTE UNION ALL
SELECT 'D', 1, 12 FROM CTE
), CTE(DUMMY) AS (SELECT 'X')
SELECT T.*,
RANDOM(1, 7) --HERE'S THE PROBLEM
FROM TRIAL T;

I'm pretty sure there's simple solution for this. Greatly appreciate any help.

Best Answer

This will return a number within the requested range:

RANDOM(0, 2147483647) mod (T.VAL_MAX - T.VAL_MIN +1 ) + T.VAL_MIN

But there might be a minimal bias towards the lower numbers due to MOD, e.g. for 1-3 you get following probablities:

1,2 = 715,827,883 / 2,147,483,648 
  3 = 715,827,882 / 2,147,483,648
Related Question