Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, October 24, 2012 12:45 PM
Why this query return NULL some of the times?
I have used 'case' statement to generate random number between 1 to 50.
select
case ROUND(((50 - 1 -1) * RAND() + 1), 0)
when 1 then 'New York'
when 2 then 'Los Angeles'
when 3 then 'Chicago'
when 4 then 'Houston'
when 5 then 'Philadelphia'
when 6 then 'Phoenix'
when 7 then 'San Antonio'
when 8 then 'San Diego'
when 9 then 'Dallas'
when 10 then 'San Jose'
when 11 then 'Jacksonville'
when 12 then 'Indianapolis'
when 13 then 'San Francisco'
when 14 then 'Austin'
when 15 then 'Columbus'
when 16 then 'Fort Worth'
when 17 then 'Charlotte'
when 18 then 'Detroit'
when 19 then 'El Paso'
when 20 then 'Memphis'
when 21 then 'Baltimore'
when 22 then 'Boston'
when 23 then 'Seattle'
when 24 then 'Washington'
when 25 then 'Nashville-Davidson'
when 26 then 'Denver'
when 27 then 'Louisville'
when 28 then 'Milwaukee'
when 29 then 'Portland'
when 30 then 'Las Vegas'
when 31 then 'Oklahoma City'
when 32 then 'Albuquerque'
when 33 then 'Tucson'
when 34 then 'Fresno'
when 35 then 'Sacramento'
when 36 then 'Long Beach'
when 37 then 'Kansas City'
when 38 then 'Mesa'
when 39 then 'Virginia Beach'
when 40 then 'Atlanta'
when 41 then 'Colorado Springs'
when 42 then 'Omaha'
when 43 then 'Raleigh'
when 44 then 'Miami'
when 45 then 'Cleveland'
when 46 then 'Tulsa'
when 47 then 'Oakland'
when 48 then 'Minneapolis'
when 49 then 'Wichita'
when 50 then 'Arlington'
end
/w
Wednesday, October 24, 2012 3:56 PM ✅Answered
I am pretty sure there was an interesting discussion on this exact topic several months back, I'll run a quick search.
UPDATE. Found it http://social.msdn.microsoft.com/Forums/en/transactsql/thread/3f430d6d-ebe6-4219-9c3e-27414a38130c
and the answer from sandhyadevineni explains it.
For every expert, there is an equal and opposite expert. - Becker's Law
Wednesday, October 24, 2012 4:00 PM ✅Answered | 1 vote
The return value NULL is a design behavior when you use Rand() function in CASE Expression (it seems it is inherited from Sybase product and my guess is a standard compliance behavior). You will get some unexpected results, in your case it is the NULL value.
You can use a variable to pass the value to use in the CASE Expression:
Declare @num int=CAST((50 * RAND() + 1) as int) SELECT CASE @numwhen 1 then 'New York'when 2 then 'Los Angeles'--..ELSE'Unknown'END
You can refer to this:(Search term:"Using rand() functions in case expressions")
Thursday, October 25, 2012 8:38 AM ✅Answered
Ok! so the syntax of CASE statement is quite precarious with some functions like Rand().
I opted for
case RandomNumber()
when 1 then '1'
when 2 then '2'
end
for the obvious reason... I was looking for a 'Single' random number once for an execution.
But, as you guys have pointed out, CASE is translated to
case
when RandomNumber()=1 then '1'
when RandomRumber()=2 then '2'
end
And that was not required here in my scenario. (neither default/else is required here)
Wrapping it in a view, forced it to produce a single value; as pointed out first by Naomi and then Tom Phillips.
select case Rn
when 1 then '1'
when 2 then '2' -- and so on.
end from (Select ROUND(((3 - 1 -1) * Rand(Cast(newid() as varbinary)) + 1), 0) as Rn) aView
johnqflorida, you side-note is quite helpful otherwise I will never be able to reach 'Arlington' :)
Providing random GUID as a seed value is a good idea to generate random values for ROW in updates and insertions.
like in my case
update participant set city =
(
select case Rn
when 1 then '1'
when 2 then '2'
end from (Select ROUND(((3 - 1 -1) * Rand(Cast(newid() as varbinary)) + 1), 0) as Rn) aView
)
Thank you all !
/w
Wednesday, October 24, 2012 12:54 PM
I believe we had a similar thread a while back, but I don't recall details. I got NULL the first time I ran it, but then I changed it this way and no longer getting NULL:
;with cte as (Select ROUND(((50 - 1 -1) * RAND() + 1), 0) as RandomNumber)
select RandomNumber,
case RandomNumber
when 1 then 'New York'
when 2 then 'Los Angeles'
when 3 then 'Chicago'
when 4 then 'Houston'
when 5 then 'Philadelphia'
when 6 then 'Phoenix'
when 7 then 'San Antonio'
when 8 then 'San Diego'
when 9 then 'Dallas'
when 10 then 'San Jose'
when 11 then 'Jacksonville'
when 12 then 'Indianapolis'
when 13 then 'San Francisco'
when 14 then 'Austin'
when 15 then 'Columbus'
when 16 then 'Fort Worth'
when 17 then 'Charlotte'
when 18 then 'Detroit'
when 19 then 'El Paso'
when 20 then 'Memphis'
when 21 then 'Baltimore'
when 22 then 'Boston'
when 23 then 'Seattle'
when 24 then 'Washington'
when 25 then 'Nashville-Davidson'
when 26 then 'Denver'
when 27 then 'Louisville'
when 28 then 'Milwaukee'
when 29 then 'Portland'
when 30 then 'Las Vegas'
when 31 then 'Oklahoma City'
when 32 then 'Albuquerque'
when 33 then 'Tucson'
when 34 then 'Fresno'
when 35 then 'Sacramento'
when 36 then 'Long Beach'
when 37 then 'Kansas City'
when 38 then 'Mesa'
when 39 then 'Virginia Beach'
when 40 then 'Atlanta'
when 41 then 'Colorado Springs'
when 42 then 'Omaha'
when 43 then 'Raleigh'
when 44 then 'Miami'
when 45 then 'Cleveland'
when 46 then 'Tulsa'
when 47 then 'Oakland'
when 48 then 'Minneapolis'
when 49 then 'Wichita'
when 50 then 'Arlington'
end as City
from cte
For every expert, there is an equal and opposite expert. - Becker's Law
Wednesday, October 24, 2012 1:40 PM
Just a sidenote... You're generating integers between 1 and 49, not 1 and 50. Change to 50 -1, not 50 -1 -1. That doesn't necessarily explain the null, but you're currently never assigning 50/Arlington.
Here's a statistical test of whatever random function you try:
Select min(X), max(X), avg(X), stdev(X), count(X), count(case when x is null then 1 else null end)
, min(y), max(y), avg(y), stdev(y), count(y), count(case when y is null then 1 else null end)
from
(Select top 10000
ROUND(((50 - 1 - 1 ) * RAND(Cast(newid() as varbinary)) + 1), 0) as Y
, 1 + Round( 49 * Rand(Cast(newid() as varbinary)), 0 ) as X
from sys.messages) DT
Wednesday, October 24, 2012 2:37 PM
Hi Naomi, I do not understand what difference it made having a cte not throwing NULL whereas just SELECT statement which would throw occasional NULL values.
Thanks............
Wednesday, October 24, 2012 2:46 PM
Try
select
case ROUND((50 * RAND()+0.5), 0)
when 1 then 'New York'
when 2 then 'Los Angeles'
when 3 then 'Chicago'
when 4 then 'Houston'
when 5 then 'Philadelphia'
when 6 then 'Phoenix'
when 7 then 'San Antonio'
when 8 then 'San Diego'
when 9 then 'Dallas'
when 10 then 'San Jose'
when 11 then 'Jacksonville'
when 12 then 'Indianapolis'
when 13 then 'San Francisco'
when 14 then 'Austin'
when 15 then 'Columbus'
when 16 then 'Fort Worth'
when 17 then 'Charlotte'
when 18 then 'Detroit'
when 19 then 'El Paso'
when 20 then 'Memphis'
when 21 then 'Baltimore'
when 22 then 'Boston'
when 23 then 'Seattle'
when 24 then 'Washington'
when 25 then 'Nashville-Davidson'
when 26 then 'Denver'
when 27 then 'Louisville'
when 28 then 'Milwaukee'
when 29 then 'Portland'
when 30 then 'Las Vegas'
when 31 then 'Oklahoma City'
when 32 then 'Albuquerque'
when 33 then 'Tucson'
when 34 then 'Fresno'
when 35 then 'Sacramento'
when 36 then 'Long Beach'
when 37 then 'Kansas City'
when 38 then 'Mesa'
when 39 then 'Virginia Beach'
when 40 then 'Atlanta'
when 41 then 'Colorado Springs'
when 42 then 'Omaha'
when 43 then 'Raleigh'
when 44 then 'Miami'
when 45 then 'Cleveland'
when 46 then 'Tulsa'
when 47 then 'Oakland'
when 48 then 'Minneapolis'
when 49 then 'Wichita'
when 50 then 'Arlington'
end
Many Thanks & Best Regards, Hua Min
Wednesday, October 24, 2012 3:07 PM
(Possible repost, my earlier post seemed to have disappeared)
Strange.. this also never returns nulls, just from realizing the rand() output in a derived table (see the end). Weird compiler anomaly, where nobody expects you to just "Select something" without it being from a table?
select /* randno, ROUND(((50 - 1 ) * RANDNo + 1), 0), */ /* uncomment for interesting diagnostics */
case (Select ROUND( ( (50 - 1) * RANDNO + 1), 0) as RandNo2)
when 1 then 'New York'
when 2 then 'Los Angeles'
when 3 then 'Chicago'
when 4 then 'Houston'
when 5 then 'Philadelphia'
when 6 then 'Phoenix'
when 7 then 'San Antonio'
when 8 then 'San Diego'
when 9 then 'Dallas'
when 10 then 'San Jose'
when 11 then 'Jacksonville'
when 12 then 'Indianapolis'
when 13 then 'San Francisco'
when 14 then 'Austin'
when 15 then 'Columbus'
when 16 then 'Fort Worth'
when 17 then 'Charlotte'
when 18 then 'Detroit'
when 19 then 'El Paso'
when 20 then 'Memphis'
when 21 then 'Baltimore'
when 22 then 'Boston'
when 23 then 'Seattle'
when 24 then 'Washington'
when 25 then 'Nashville-Davidson'
when 26 then 'Denver'
when 27 then 'Louisville'
when 28 then 'Milwaukee'
when 29 then 'Portland'
when 30 then 'Las Vegas'
when 31 then 'Oklahoma City'
when 32 then 'Albuquerque'
when 33 then 'Tucson'
when 34 then 'Fresno'
when 35 then 'Sacramento'
when 36 then 'Long Beach'
when 37 then 'Kansas City'
when 38 then 'Mesa'
when 39 then 'Virginia Beach'
when 40 then 'Atlanta'
when 41 then 'Colorado Springs'
when 42 then 'Omaha'
when 43 then 'Raleigh'
when 44 then 'Miami'
when 45 then 'Cleveland'
when 46 then 'Tulsa'
when 47 then 'Oakland'
when 48 then 'Minneapolis'
when 49 then 'Wichita'
when 50 then 'Arlington'
end
from
(select rand() as RandNo) DT
EDIT: Another example: This also demonstrates the oddity. It only happens within the CASE statement, so "CASERESULTS" can return a null, whereas "JustTheString" never does. (NewID() returns a GUID, beginning always with 0-9 or A-F)
Select Case Substring(Cast(NewID() as VarChar(99)), 1, 1)
when '0' then 'True'
when '1' then 'True'
when '2' then 'True'
when '3' then 'True'
when '4' then 'True'
when '5' then 'True'
when '6' then 'True'
when '7' then 'True'
when '8' then 'True'
when '9' then 'True'
when 'A' then 'True'
when 'B' then 'True'
when 'C' then 'True'
when 'D' then 'True'
when 'E' then 'True'
when 'F' then 'True'
End as CASERESULTS
, Substring(Cast(NewId() as varchar(99)), 1, 1) as JustTheString
Wednesday, October 24, 2012 3:30 PM
Hi, I am not sure if this is an expected behaviour or something wierd
--Stmt 1 SELECT RAND(100), RAND(), ROUND(((50 - 1 -1) * RAND() + 1), 0) GO --Stmt 2 SELECT RAND(), ROUND(((50 - 1 -1) * RAND() + 1), 0) G
when I execute the 2 SELECT statements repeatedly I get same results but executing just the 2nd statement returns different results for each execution.
Wednesday, October 24, 2012 3:59 PM
Hi, I am not sure if this is an expected behaviour or something wierd
--Stmt 1 SELECT RAND(100), RAND(), ROUND(((50 - 1 -1) * RAND() + 1), 0) GO --Stmt 2 SELECT RAND(), ROUND(((50 - 1 -1) * RAND() + 1), 0) Gwhen I execute the 2 SELECT statements repeatedly I get same results but executing just the 2nd statement returns different results for each execution.
Expected... the part where you called "RAND(100)" represents using a seed to the Rand call, to produce repeatable results. Once you call the seeded Rand once, subsequent calls follow also a predictable pattern.
Wednesday, October 24, 2012 4:32 PM
Learn something new every day! Thanks Jinyang & Naomi!
Wednesday, October 24, 2012 5:14 PM
Just to clarify the answer in this thread. This is expected behavior because RAND() is reevaluated in every WHEN clause. Your code is equvalant to:
select case when ROUND(((50 - 1 -1) * RAND() + 1), 0) = 1 then 'New York' when ROUND(((50 - 1 -1) * RAND() + 1), 0) = 2 then 'Los Angeles' when ROUND(((50 - 1 -1) * RAND() + 1), 0) = 3 then 'Chicago' when ROUND(((50 - 1 -1) * RAND() + 1), 0) = 4 then 'Houston' when ROUND(((50 - 1 -1) * RAND() + 1), 0) = 5 then 'Philadelphia' when ROUND(((50 - 1 -1) * RAND() + 1), 0) = 6 then 'Phoenix'....
Wednesday, October 24, 2012 5:31 PM
Hi,
Add an else clause
select
case ROUND(((50 - 1 -1) * RAND() + 1), 0)
when 1 then 'New York'
when 50 then 'Arlington'
else 'Some default string'
end
Regards.
Please remember to mark the replies as answers if they help and unmark them if they provide no help , or you may vote-up a helpful post