Choose returns null

user_3800287 0
Viewed 108
SELECT
    r.[value] as KnownRandomNumber,
    CHOOSE(r.[value], 5, 15, 16, 17) AS KnownRandomResult,
    CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17) AS UnknownRandomResult
FROM
    (SELECT FLOOR(RAND() * 4) + 1 AS [value]) r

Why does the CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17) sometimes return null? Even though using the random number obtained from subquery never returns null?

I'm running this on Microsoft SQL Server in SQL Server Management Studio.

enter image description here

random sql-server sql database
1 Answers

I think you are out of index. And you are asumming that RAND() returns the same value for each execution of the same query when NOT. Problem is about the logic here, not the function 'CHOOSE'

Test and play with this and see the values of every field and how it works:

SELECT
    CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17,'OUT') AS UnknownRandomResult,
    RAND() * 4 as Rand4,
    FLOOR(RAND() * 4)  as Floor4,
    FLOOR(RAND() * 4) + 1 as Floor4Plus1,
        RAND() * 4 as Rand4_2,
    FLOOR(RAND() * 4)  as Floor4_2,
    FLOOR(RAND() * 4) + 1 as Floor4Plus1_2

More info to read: https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-choose-transact-sql?view=sql-server-ver15

UPDATE:

I suggest fix that NULL with some IF NULL default value.

user_8884720
Related