In Teradata SQL how to get random number between a pair, coming from field/column?

1 vote

1 answer

721 views

                          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.
                        

Asked by Rino (229 rep)

Mar 5, 2021, 07:56 AM
Last activity: Mar 8, 2021, 07:13 PM

In Teradata SQL how to get random number between a pair, coming from field/column?

Related Questions