Databases
StackOverflow2013
Postsdbo
POStrange behaviour of CASE construction
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStrange behaviour of CASE construction
    primarykey
    data
    text
    <p>Background : I was trying to get some random 'hex' values while creating dummy data and came up with this construction :</p> <pre><code>SELECT TOP 100 result = (CASE ABS(Binary_Checksum(NewID())) % 16 WHEN -1 THEN 'hello' WHEN 0 THEN '0' WHEN 1 THEN '1' WHEN 2 THEN '2' WHEN 3 THEN '3' WHEN 4 THEN '4' WHEN 5 THEN '5' WHEN 6 THEN '6' WHEN 7 THEN '7' WHEN 8 THEN '8' WHEN 9 THEN '9' WHEN 10 THEN 'a' WHEN 11 THEN 'b' WHEN 12 THEN 'c' WHEN 13 THEN 'd' WHEN 14 THEN 'e' WHEN 15 THEN 'f' ELSE 'huh' END) FROM sys.objects </code></pre> <p>When running this on my SQL Server 2008 R2 instance, I get quite a lot of 'huh' records:</p> <pre><code>result ------ huh 3 huh huh 6 8 6 </code></pre> <p>I really don't understand why. What I would expect to happen is :</p> <ul> <li>for every record <code>NewID()</code> comes up with a new <em>random</em> value </li> <li><code>Binary_Checksum()</code> calculates an int based on said value</li> <li><code>ABS()</code> makes the value positive</li> <li><code>% 16</code> returns the remainder of that positive value if it would be divided by 16, which then would be a value between 0 and 15</li> <li>the <code>CASE</code> construction converts the value to a relevant character</li> <li>Since there are <code>WHEN</code>s for every value between 0 and 15, the <code>ELSE</code> should never be needed</li> </ul> <p>or at least, that is what I would think should happen... but obviously something goes wrong along the road...</p> <p>When doing the same thing in a two-step approach (via temp-table), the huh's are gone...</p> <pre><code>SELECT TOP 100 x = ABS(Binary_Checksum(NewID())) % 16, result = 'hello' INTO #test FROM sys.objects UPDATE #test SET result = (CASE x WHEN 0 THEN '0' WHEN 1 THEN '1' WHEN 2 THEN '2' WHEN 3 THEN '3' WHEN 4 THEN '4' WHEN 5 THEN '5' WHEN 6 THEN '6' WHEN 7 THEN '7' WHEN 8 THEN '8' WHEN 9 THEN '9' WHEN 10 THEN 'a' WHEN 11 THEN 'b' WHEN 12 THEN 'c' WHEN 13 THEN 'd' WHEN 14 THEN 'e' WHEN 15 THEN 'f' ELSE 'huh' END) SELECT * FROM #test </code></pre> <p>Anyone who understands this ? As far as I can tell it should give the same result (it IS copy-paste indeed) regardless of me doing it directly or via a temp-table... But obviously something goes wrong if I do it in a single statement.</p> <p>PS: I don't need a 'fix' for this, I already have a workaround (see below), I merely am hoping someone can explain me why this does what it does.</p> <p>Workaround :</p> <pre><code>SELECT TOP 100 result = SubString('0123456789abcdef', 1 + (ABS(Binary_Checksum(NewID())) % 16), 1) FROM sys.objects </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJon Egerton
    1. USderoby
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStrange behaviour of CASE construction
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POStrange behaviour of CASE construction
      1. USFilip De Vos
      1. VTFavorite
    3. VO
      singulars
      1. POStrange behaviour of CASE construction
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPS: if anyone could test this on different versions of SQL-Server that might be interesting too...
      singulars
      1. POStrange behaviour of CASE construction
      1. USderoby
    2. COYour workaround looks more succinct than the original...
      singulars
      1. POStrange behaviour of CASE construction
      1. USRussell
    3. COThx for explaining this guys...going with Damiens answer as it is a bit more verbose for anyone who might stumble upon this. (and he was 1 minute faster on top =P )
      singulars
      1. POStrange behaviour of CASE construction
      1. USderoby
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload