Groups > comp.databases.ms-sqlserver > #1850

Re: CASE issue

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!news.swapon.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: CASE issue
Date	Fri, 28 Nov 2014 22:47:47 +0100
Organization	Erland Sommarskog
Lines	49
Message-ID	<XnsA3F3E7E5D4C04Yazorman@127.0.0.1> (permalink)
References	<547306f8$0$41749$c3e8da3$5d8fb80f@news.astraweb.com> <XnsA3EF7E8B63B6CYazorman@127.0.0.1> <3fc10276-8769-4885-ad67-1a5aee9dbb5c@googlegroups.com> <XnsA3EF9D686D618Yazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx02.eternal-september.org; posting-host="416ef8a880b1e4f6f96e9a7c39a8d7a9"; logging-data="19122"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX195PA3auqGq9VRkB+kM9FSB"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:wfztfBNl/VpYDhpiJDmkPSU9UGI=
Xref	csiph.com comp.databases.ms-sqlserver:1850

Show key headers only | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web