Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1840 > unrolled thread

CASE issue

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  CASE issue "twenty-six@b-mint.net" <twenty-six@b-mint.net> - 2014-11-24 10:22 +0000
    Re: CASE issue Erland Sommarskog <esquel@sommarskog.se> - 2014-11-24 11:26 +0000
      Re: CASE issue bradbury9 <ray.bradbury9@gmail.com> - 2014-11-24 04:16 -0800
        Re: CASE issue Erland Sommarskog <esquel@sommarskog.se> - 2014-11-24 14:28 +0000
          Re: CASE issue Erland Sommarskog <esquel@sommarskog.se> - 2014-11-28 22:47 +0100
            Re: CASE issue Jim <jgeissman@socal.rr.com> - 2014-11-28 17:19 -0800
      Re: CASE issue "twenty-six@b-mint.net" <twenty-six@b-mint.net> - 2014-11-24 13:32 +0000
        Re: CASE issue Erland Sommarskog <esquel@sommarskog.se> - 2014-11-24 14:25 +0000
          Re: CASE issue "twenty-six@b-mint.net" <twenty-six@b-mint.net> - 2014-11-24 16:06 +0000
      Re: CASE issue Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2014-11-24 17:18 +0100
        Re: CASE issue Erland Sommarskog <esquel@sommarskog.se> - 2014-11-24 23:37 +0100
          Re: CASE issue Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2014-11-25 06:25 +0100
            Re: CASE issue Jim <jgeissman@socal.rr.com> - 2014-11-28 16:07 -0800
              Re: CASE issue Erland Sommarskog <esquel@sommarskog.se> - 2014-11-29 10:43 +0100

#1840 — CASE issue

Hello,   I'm trying to write a query to anonymise some data so need to 
generate a random birth month.

I'm generating random numbers okay, but am trying to decode the month 
with case statement:

select
case   cast(rand(checksum(newid()))*12 as int)+1
		when 1  then 'Jan'
		when 2  then 'Feb'
		when 3  then 'Mar'
		when 4  then 'Apr'
		when 5  then 'May'
		when 6  then 'Jun'
		when 7  then 'Jul'
		when 8  then 'Aug'
		when 9  then 'Sep'
		when 10 then 'Oct'
		when 11 then 'Nov'
		when 12 then 'Dec'
		when 13 then 'was 13!'
		when 0 then 'was 0!'
		else 'Unknown'
end
  from sysobjects


When I run this query I get lots of 'Unknown'  values, but I'm not sure 
why. If I just select distinct cast(rand(checksum(newid()))*12 as int)+1 
  I get the values 1-12 as expected.   It's not 0 or 13,  and if I put 
an ifnull to convert null to 13 then I still don't get any 'was 13!' 
entries.

So what am I missing?

Thanks,

Michael

[toc] | [next] | [standalone]

#1841

twenty-six@b-mint.net (twenty-six@b-mint.net) writes:
> When I run this query I get lots of 'Unknown'  values, but I'm not sure 
> why. 

This happens because

    CASE expr WHEN val1 THEN ....
              WHEN val2 THEN ....

is a shortcut for

   CASE WHEN expr = val1 THEN
        WHEN expr = val2 THEN

and thus the expression gets evaluated multiple times and then it goes 
downhill from there.

Set up a mapping table instead.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1842

El lunes, 24 de noviembre de 2014 12:26:31 UTC+1, Erland Sommarskog  escribió:
> twenty-six writes:
> > When I run this query I get lots of 'Unknown'  values, but I'm not sure 
> > why. 
> 
> This happens because
> 
>     CASE expr WHEN val1 THEN ....
>               WHEN val2 THEN ....
> 
> is a shortcut for
> 
>    CASE WHEN expr = val1 THEN
>         WHEN expr = val2 THEN
> 
> and thus the expression gets evaluated multiple times and then it goes 
> downhill from there.
> 
> Set up a mapping table instead.
> 

In the MSDN doc it does not mention anything about being a shortcut, and I supposed it was not the case. Good to know that to avoid future errors.

> -- 
> Erland Sommarskog, SQL Server MVP
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1845

bradbury9 (ray.bradbury9@gmail.com) writes:
> In the MSDN doc it does not mention anything about being a shortcut, and
> I supposed it was not the case. Good to know that to avoid future
> errors. 

Indeed. Books Online leaves no doubt that it works like twenty-six expected.
I need to discuss this with Microsoft. Will first have to check older 
versions.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1850

Erland Sommarskog (esquel@sommarskog.se) writes:
> Indeed. Books Online leaves no doubt that it works like twenty-six
> expected. I need to discuss this with Microsoft. Will first have to
> check older versions. 

It seems like in SQL 6.5 it works like described in Books Online. SQL 2000
works like later versions as does SQL 7. Although, note that there are 
two patterns here: newid() and a subquery like in:

 select
   case  (SELECT a FROM bluddrig)
         when 1  then 'Jan'
         when 2  then 'Feb'
         when 3  then 'Mar'
         when 4  then 'Apr'
         when 5  then 'May'
         when 6  then 'Jun'
         when 7  then 'Jul'
         when 8  then 'Aug'
         when 9  then 'Sep'
         when 10 then 'Oct'
         when 11 then 'Nov'
         when 12 then 'Dec'
         when 13 then 'was 13!'
         when 0 then 'was 0!'
         else 'Unknown'
   end
     from sysobjects


newid() is overall special, since it's reevaluated for every row, while
other built-in functions are evaluated once - at least as they only appear
once.

There is no newid() on SQL 6.5, so I can say what happens there. But on SQL 
7 and later, the query with the subquery, has 14 access to the table 
bluddrig - SQL 6.5 has one.

I have not gotten any response, but the likelihood that Microsoft would
accept this as a regression from 6.5 and change it is minimal. Not the
least since there is a possible backwards compatibility here.

So I have filed a doc bug: 
https://connect.microsoft.com/SQLServer/feedback/details/1042044



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1852

In article <XnsA3F3E7E5D4C04Yazorman@127.0.0.1>, esquel@sommarskog.se 
says...
> 
> Erland Sommarskog (esquel@sommarskog.se) writes:
> > Indeed. Books Online leaves no doubt that it works like twenty-six
> > expected. I need to discuss this with Microsoft. Will first have to
> > check older versions. 
> 
> It seems like in SQL 6.5 it works like described in Books Online. SQL 2000
> works like later versions as does SQL 7. Although, note that there are 
> two patterns here: newid() and a subquery like in:
> 
>  select
>    case  (SELECT a FROM bluddrig)
>          when 1  then 'Jan'
>          when 2  then 'Feb'
>          when 3  then 'Mar'
>          when 4  then 'Apr'
>          when 5  then 'May'
>          when 6  then 'Jun'
>          when 7  then 'Jul'
>          when 8  then 'Aug'
>          when 9  then 'Sep'
>          when 10 then 'Oct'
>          when 11 then 'Nov'
>          when 12 then 'Dec'
>          when 13 then 'was 13!'
>          when 0 then 'was 0!'
>          else 'Unknown'
>    end
>      from sysobjects
> 
> 
> newid() is overall special, since it's reevaluated for every row, while
> other built-in functions are evaluated once - at least as they only appear
> once.
> 
> There is no newid() on SQL 6.5, so I can say what happens there. But on SQL 
> 7 and later, the query with the subquery, has 14 access to the table 
> bluddrig - SQL 6.5 has one.
> 
> I have not gotten any response, but the likelihood that Microsoft would
> accept this as a regression from 6.5 and change it is minimal. Not the
> least since there is a possible backwards compatibility here.
> 
> So I have filed a doc bug: 
> https://connect.microsoft.com/SQLServer/feedback/details/1042044

Very good.

[toc] | [prev] | [next] | [standalone]

#1843

On 24/11/2014 11:26, Erland Sommarskog wrote:
> twenty-six@b-mint.net (twenty-six@b-mint.net) writes:
>> When I run this query I get lots of 'Unknown'  values, but I'm not sure
>> why.
>
> This happens because
>
>      CASE expr WHEN val1 THEN ....
>                WHEN val2 THEN ....
>
> is a shortcut for
>
>     CASE WHEN expr = val1 THEN
>          WHEN expr = val2 THEN
>
> and thus the expression gets evaluated multiple times and then it goes
> downhill from there.
>
> Set up a mapping table instead.
>
>
Ah I see, many thanks.    That would explain it nicely,  I really 
thought I'd found a bug.   :-)

By mapping table you mean create a table,  insert 12 rows  with  1, Jan 
   2, Feb  etc.  then join with that?

[toc] | [prev] | [next] | [standalone]

#1844

twenty-six@b-mint.net (twenty-six@b-mint.net) writes:
> By mapping table you mean create a table,  insert 12 rows  with  1, Jan 
>    2, Feb  etc.  then join with that?
> 

Precisely.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1846

On 24/11/2014 14:25, Erland Sommarskog wrote:
> twenty-six@b-mint.net (twenty-six@b-mint.net) writes:
>> By mapping table you mean create a table,  insert 12 rows  with  1, Jan
>>     2, Feb  etc.  then join with that?
>>
>
> Precisely.
>
Thanks.  Was just checking that I wasn't missing something "clever". 
The job's all done now.

Cheers,

Michael

[toc] | [prev] | [next] | [standalone]

#1847

On 11/24/2014 12:26 PM, Erland Sommarskog wrote:
> twenty-six@b-mint.net (twenty-six@b-mint.net) writes:
>> When I run this query I get lots of 'Unknown'  values, but I'm not sure
>> why.
>
> This happens because
>
>      CASE expr WHEN val1 THEN ....
>                WHEN val2 THEN ....
>
> is a shortcut for
>
>     CASE WHEN expr = val1 THEN
>          WHEN expr = val2 THEN
>
> and thus the expression gets evaluated multiple times and then it goes
> downhill from there.
>

I think (don't have time to look it up now) the SQL-standard says that 
the result of the CASE statement is the first condition that evaluates 
to true. From your answer I get the impression that this is not true for 
SQL-server, correct?

Surely only one value is returned, but is it indeterministic which one?


/Lennart

[...]

[toc] | [prev] | [next] | [standalone]

#1848

Lennart Jonsson (erik.lennart.jonsson@gmail.com) writes:
> I think (don't have time to look it up now) the SQL-standard says that 
> the result of the CASE statement is the first condition that evaluates 
> to true. From your answer I get the impression that this is not true for 
> SQL-server, correct?

That is indeed true. The issue is that the expression on the left-hand side 
is reevaluated for every branch in the CASE. 

newid() is a function that returns a randomly generated GUID, so it is a bit 
of a special case.
 



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1849

On 11/24/2014 11:37 PM, Erland Sommarskog wrote:
> Lennart Jonsson (erik.lennart.jonsson@gmail.com) writes:
>> I think (don't have time to look it up now) the SQL-standard says that
>> the result of the CASE statement is the first condition that evaluates
>> to true. From your answer I get the impression that this is not true for
>> SQL-server, correct?
>
> That is indeed true. The issue is that the expression on the left-hand side
> is reevaluated for every branch in the CASE.
>

I see, thanks


/Lennart

[toc] | [prev] | [next] | [standalone]

#1851

In article <m513rc$8vr$1@dont-email.me>, erik.lennart.jonsson@gmail.com 
says...
> 
> On 11/24/2014 11:37 PM, Erland Sommarskog wrote:
> > Lennart Jonsson (erik.lennart.jonsson@gmail.com) writes:
> >> I think (don't have time to look it up now) the SQL-standard says that
> >> the result of the CASE statement is the first condition that evaluates
> >> to true. From your answer I get the impression that this is not true for
> >> SQL-server, correct?
> >
> > That is indeed true. The issue is that the expression on the left-hand side
> > is reevaluated for every branch in the CASE.
> >
> 
> I see, thanks
> 
> 
> /Lennart

I believe RAND() is evaluated once at the beginning, while NEWID() is 
evaluated each time. So one solution would be to evaluate the NEWID() 
version and park the result in a variable and use the variable, or use 
RAND() but base it on a seed that doesn't involve NEWID(), or at least 
if it does, determine it outside of the CASE statement.

[toc] | [prev] | [next] | [standalone]

#1853

Jim (jgeissman@socal.rr.com) writes:
> I believe RAND() is evaluated once at the beginning, while NEWID() is 
> evaluated each time. So one solution would be to evaluate the NEWID() 
> version and park the result in a variable and use the variable, or use 
> RAND() but base it on a seed that doesn't involve NEWID(), or at least 
> if it does, determine it outside of the CASE statement.
> 

I don't think that would work well, because you want a new random number
per row. You could use a column on the row as seed - but then it would 
be deterministic. Look at this.

SELECT rand(), rand(object_id) FROM sys.objects


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web