Issue with CASE on Random Number

Question

Issue with CASE on Random Number

planetmatt

SSCrazy

Points: 2207
More actions
March 10, 2016 at 9:51 am

#308930

I am creating some scripts to create a large number of fake data rows for performance testing.
Below is a snippet of my code which creates the data. It uses a couple of CTEs to create data out of nothing like an old tally table.
DECLARE
@Start INT, @End INT, @FromDate DATETIME, @ToDate DATETIME
SELECT
@Start=1
, @End=1000 -- Records per Day
, @FromDate = '2016-01-01 00:00:00'
, @ToDate = '2016-01-04 23:59:59' -- Date Range
IF OBJECT_ID('tempdb..#RowGen') IS NOT NULL
DROP TABLE #RowGen
;WITH NumberSequence(Number) AS
(
SELECT @start AS Number
UNION ALL
SELECT Number + 1
FROM NumberSequence
WHERE Number < @end
),
DateSequence(Date) AS
(
SELECT @FromDate AS Date
UNION ALL
SELECT DATEADD(day,1,Date)
FROM DateSequence
WHERE Date < @ToDate
)
SELECT ROW_NUMBER() OVER (ORDER BY DateSequence.Date,NumberSequence.Number) AS RowSequence,DateSequence.DATE AS DateSequence
INTO #RowGen
FROM NumberSequence
CROSS JOIN DateSequence
Option (MaxRecursion 32767)
SELECT
RowSequence AS CaseID
,DateSequenceas [CaseDate]
,CAST(((8 + 1) - 1) *RAND(CHECKSUM(NEWID())) + 1 AS TINYINT)
,CASE CAST(((8 + 1) - 1) *RAND(CHECKSUM(NEWID())) + 1 AS TINYINT)
WHEN1THEN'Black'
WHEN2THEN'White'
WHEN3THEN'Red'
WHEN4THEN'Blue'
WHEN5THEN'Silver'
WHEN6THEN'Grey'
WHEN7THEN'Green'
WHEN8THEN'Yellow'
ENDas [VehicleColour]
FROM #RowGen
IF OBJECT_ID('tempdb..#RowGen') IS NOT NULL
DROP TABLE #RowGen
I'm using CAST(((8 + 1) - 1) *RAND(CHECKSUM(NEWID())) + 1 AS TINYINT) to give me a random number between 1 and 8. This works fine.
I then try to apply a CASE to this number to display a Colour based on the number output. However when running this, the CASE displays many NULL values and one colour might get mapped to two different numbers.
Anybody know what is going wrong here?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

I've seen stuff about this before. The expression in the CASE isn't necessarily evaluated once and then that result used for each comparison in the CASE, as you might expect.

Instead, it ends up getting rewritten such that the random value is reevaluated for each comparison, so you can get cases where all comparisons fail because each comparison in the CASE uses a new random number.

The simplest fix is just to persist the randomly generated value, say in a temp table, and then use that persisted value.

Cheers!

ScottPletcher SSC Guru Points: 101118 More actions · Answer 2

Try using a CROSS APPLY to assign an alias to the random number:

SELECT

RowSequence AS CaseID

,DateSequenceas [CaseDate]

,random_number

,CASE random_number

WHEN1THEN'Black'

WHEN2THEN'White'

WHEN3THEN'Red'

WHEN4THEN'Blue'

WHEN5THEN'Silver'

WHEN6THEN'Grey'

WHEN7THEN'Green'

WHEN8THEN'Yellow'

ENDas [VehicleColour]

FROM #RowGen

CROSS APPLY (

SELECT CAST(((8 + 1) - 1) *RAND(CHECKSUM(NEWID())) + 1 AS TINYINT) AS random_number

) AS assign_alias_names

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".