May 29, 2014 at 3:38 pm
In existing t-sql 2008, there is the following sql that I am trying to understand so that I can modify it. The field that is called tMask is definitely as an integer. The values stored in this field are hexadecimal values. The values indicate what fields contain a check box in them, For example, if checkbox 1 is selected then the value in this field = 1. If the value is 3, then the first and second checkboxes have been selected.
select
, replace(rtrim(
case when max(case when b = 0 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '1 ' else '' end
+ case when max(case when b = 1 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '2 ' else '' end
+ case when max(case when b = 2 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '3 ' else '' end
+ case when max(case when b = 3 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '4 ' else '' end
+ case when max(case when b = 4 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '5 ' else '' end
+ case when max(case when b = 5 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '6 ' else '' end
+ case when max(case when b = 6 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '7 ' else '' end
+ case when max(case when b = 7 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '8 ' else '' end
+ case when max(case when b = 8 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '9 ' else '' end
+ case when max(case when b = 9 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '10 ' else '' end
+ case when max(case when b = 10 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '11 ' else '' end
+ case when max(case when b = 11 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '12 ' else '' end
),' ',',') as [tMask]
FROM [test].[dbo].[test1]
join (
select 11 as b,2048 as e union all select 10, 1024 union all select 9, 512 union all select 8, 256
union all select 7, 128 union all select 6, 64 union all select 5, 32 union all select 4, 16
union all select 3, 8 union all select 2, 4 union all select 1, 2 union all select 0, 1
) bits on 1=1
Based upon what I have said, can you tell me how to tell what the value = '42' means? What checkboxes have been selected? If the value = 1023, what checkboxes have been selected?
Can you explain to me the sql listed above?
May 29, 2014 at 4:38 pm
wendy elizabeth (5/29/2014)
In existing t-sql 2008, there is the following sql that I am trying to understand so that I can modify it. The field that is called tMask is definitely as an integer. The values stored in this field are hexadecimal values. The values indicate what fields contain a check box in them, For example, if checkbox 1 is selected then the value in this field = 1. If the value is 3, then the first and second checkboxes have been selected.select
, replace(rtrim(
case when max(case when b = 0 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '1 ' else '' end
+ case when max(case when b = 1 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '2 ' else '' end
+ case when max(case when b = 2 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '3 ' else '' end
+ case when max(case when b = 3 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '4 ' else '' end
+ case when max(case when b = 4 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '5 ' else '' end
+ case when max(case when b = 5 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '6 ' else '' end
+ case when max(case when b = 6 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '7 ' else '' end
+ case when max(case when b = 7 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '8 ' else '' end
+ case when max(case when b = 8 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '9 ' else '' end
+ case when max(case when b = 9 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '10 ' else '' end
+ case when max(case when b = 10 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '11 ' else '' end
+ case when max(case when b = 11 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '12 ' else '' end
),' ',',') as [tMask]
FROM [test].[dbo].[test1]
join (
select 11 as b,2048 as e union all select 10, 1024 union all select 9, 512 union all select 8, 256
union all select 7, 128 union all select 6, 64 union all select 5, 32 union all select 4, 16
union all select 3, 8 union all select 2, 4 union all select 1, 2 union all select 0, 1
) bits on 1=1
Based upon what I have said, can you tell me how to tell what the value = '42' means? What checkboxes have been selected? If the value = 1023, what checkboxes have been selected?
Can you explain to me the sql listed above?
I ran the query several times, finding that it effectively converts a regular decimal number into binary (base 2). The string it returns tells you which bits would be set to 1 in the binary equivalent of the input number (tMask, presumably coming from the table). When I supply multiple records in the position of the existing table, I always get the binary value of the largest value among those records. So, hexadecimal has nothing to do with it. It's just what you would see if you SELECT a small portion of the CASE statement independently, and only offers a small clue to the overall operation. So if you want the value 42 to get translated, then you need to try changing the query to replace that table name with:
(SELECT 42 AS tMask) AS X
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 3:34 am
Hi sgmunson,
can you please tell me how you inserted the below code in table format ?
(SELECT 42 AS tMask) AS X
Thanks
Bhanu
May 30, 2014 at 5:23 am
kbhanu15 (5/30/2014)
Hi sgmunson,can you please tell me how you inserted the below code in table format ?
(SELECT 42 AS tMask) AS XThanks
Bhanu
You have to use the word code, surrounded by left and right braces, ideally on it's own line, then on a line following the text for your SQL, you use the left brace, slash, the word code, and the right brace. Also, you can use the IFCode Shortcuts to the immediate left of the window where you type your message, by highlighting the text you want, then clicking on the proper shortcut. There's a specific option for SQL which has code="sql" in it. Notice that I modified your quoted message to include the feature...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 5:27 am
wendy elizabeth (5/29/2014)
...Can you explain to me the sql listed above?
A different coding method may help Steve's description:
SELECT tmask,
b1 = CASE WHEN rem2 >= 1 THEN 1 ELSE 0 END,
b2 = CASE WHEN rem3 >= 2 THEN 1 ELSE 0 END,
b3 = CASE WHEN rem4 >= 4 THEN 1 ELSE 0 END,
b4 = CASE WHEN rem5 >= 8 THEN 1 ELSE 0 END,
b5 = CASE WHEN rem6 >= 16 THEN 1 ELSE 0 END,
b6 = CASE WHEN rem7 >= 32 THEN 1 ELSE 0 END,
b7 = CASE WHEN rem8 >= 64 THEN 1 ELSE 0 END,
b8 = CASE WHEN rem9 >= 128 THEN 1 ELSE 0 END,
b9 = CASE WHEN rem10 >= 256 THEN 1 ELSE 0 END,
b10 = CASE WHEN rem11 >= 512 THEN 1 ELSE 0 END,
b11 = CASE WHEN rem12 >= 1024 THEN 1 ELSE 0 END,
b12 = CASE WHEN d.tmask >= 2048 THEN 1 ELSE 0 END,
tMask = STUFF(
CASE WHEN rem2 >= 1 THEN ',1' ELSE '' END +
CASE WHEN rem3 >= 2 THEN ',2' ELSE '' END +
CASE WHEN rem4 >= 4 THEN ',3' ELSE '' END +
CASE WHEN rem5 >= 8 THEN ',4' ELSE '' END +
CASE WHEN rem6 >= 16 THEN ',5' ELSE '' END +
CASE WHEN rem7 >= 32 THEN ',6' ELSE '' END +
CASE WHEN rem8 >= 64 THEN ',7' ELSE '' END +
CASE WHEN rem9 >= 128 THEN ',8' ELSE '' END +
CASE WHEN rem10 >= 256 THEN ',9' ELSE '' END +
CASE WHEN rem11 >= 512 THEN ',10' ELSE '' END +
CASE WHEN rem12 >= 1024 THEN ',11' ELSE '' END +
CASE WHEN d.tmask >= 2048 THEN ',12' ELSE '' END,
1,1,'')
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(17),(18),(19),(32),(37),(255)) d (tmask)
CROSS APPLY (SELECT rem12 = d.tmask % 2048) x12
CROSS APPLY (SELECT rem11 = rem12 % 1024) x11
CROSS APPLY (SELECT rem10 = rem11 % 512) x10
CROSS APPLY (SELECT rem9 = rem10 % 256) x9
CROSS APPLY (SELECT rem8 = rem9 % 128) x8
CROSS APPLY (SELECT rem7 = rem8 % 64) x7
CROSS APPLY (SELECT rem6 = rem7 % 32) x6
CROSS APPLY (SELECT rem5 = rem6 % 16) x5
CROSS APPLY (SELECT rem4 = rem5 % 8) x4
CROSS APPLY (SELECT rem3 = rem4 % 4) x3
CROSS APPLY (SELECT rem2 = rem3 % 2) x2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 30, 2014 at 5:47 am
(SELECT 42 AS tMask) AS X
May 30, 2014 at 5:49 am
Thank you very much sgmunson.
Now i am clear on this.
Sample testing.
CREATE TABLE test11
(
id INT IDENTITY (1,1),
lastupdate VARCHAR(100)
)
INSERT INTO test11 DEFAULT VALUES
SELECT count(*) FROM test11
SELECT * FROM test11
May 30, 2014 at 6:05 am
wendy elizabeth (5/29/2014)
...Can you explain to me the sql listed above?
If you want to make your code significantly faster, more compact, and more readable, try this:
SELECT
t.*,
x.[tMask_out]
FROM [test].[dbo].[test1] t
CROSS APPLY (
SELECT [tMask_out] = CAST(
STUFF(
(SELECT [text()] = ',' + CAST(n+1 AS VARCHAR(2))
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) iTally (n)
WHERE (t.[tMask]/POWER(CAST(2 AS BIGINT),iTally.n)) % 2 = 1
FOR XML PATH(''))
,1,1,'')
AS VARCHAR(20))
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 30, 2014 at 6:50 am
ChrisM,
If the original poster needed an explanation of a fairly simple query, somehow, it just doesn't seem likely that they're going to consider FOR XML PATH as something easier to understand. It no doubt is faster, but when you're at a point where basic queries can still throw you a fit, going down a more advanced road might not be the easiest first step. Also, considering the overall state of the industry and its practitioners, someone else will eventually come along who might then have to maintain that code, and who's quite likely to be mystified at the FOR XML PATH construct. I know it's handy, but it's all too often the equivalent of "rocket science" when there are more easily documented and sufficiently well-performing alternatives.
That said however, it might be a good idea to know how the original poster intends to use the query...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 7:01 am
sgmunson (5/30/2014)
ChrisM,If the original poster needed an explanation of a fairly simple query, somehow, it just doesn't seem likely that they're going to consider FOR XML PATH as something easier to understand. It no doubt is faster, but when you're at a point where basic queries can still throw you a fit, going down a more advanced road might not be the easiest first step. Also, considering the overall state of the industry and its practitioners, someone else will eventually come along who might then have to maintain that code, and who's quite likely to be mystified at the FOR XML PATH construct. I know it's handy, but it's all too often the equivalent of "rocket science" when there are more easily documented and sufficiently well-performing alternatives.
That said however, it might be a good idea to know how the original poster intends to use the query...
Hi Steve
That's why it's the second post, and not the first, which I find much easier to figure out than the OP's original code.
FOR XML PATH is now widely used, and is the best method I know of for the job it does. Can any other method be encapsulated within an iTVF? I'll defend my choice on those grounds and suggest that folks who don't know about it - should.
Point taken though, thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 30, 2014 at 7:45 am
ChrisM@Work (5/30/2014)
Hi Steve
That's why it's the second post, and not the first, which I find much easier to figure out than the OP's original code.
FOR XML PATH is now widely used, and is the best method I know of for the job it does. Can any other method be encapsulated within an iTVF? I'll defend my choice on those grounds and suggest that folks who don't know about it - should.
Point taken though, thanks.
I think we're mostly on the same page... One of the reasons I rarely use it is because I'm an IT contractor, and I always have to turn over my code to someone with considerably less SQL knowledge, so while it's a handy technique, I've found that there are often rather easy ways to avoid it. The code will be longer, but it will be a lot more maintainable in the hands of those who have to "take over" when my contract is done. I have yet to turn something over to folks who would have even the slightest clue what to do with FOR XML PATH, much less understand PIVOT all that well, so I'll hope you understand my reasoning. At least with PIVOT, I can generally get the concept across. Trying to explain FOR XML PATH is a considerably larger topic with a lot more effort required, and the one time I did explain it AND have it well understood (and to someone who's SQL skills were above average among those I typically encounter), it still took 2 weeks for them to "get it". I don't usually have that kind of luxury in terms of time available, and usually, neither do those who are taking over. I've taught SQL skills and concepts to lots of dev-types, and if you can successfully explain the workings of FOR XML PATH and consistently have it understood, then more power to ya... With the quality of the students I end up with, it could easily take a good month for them to get it, whereas they usually understand PIVOT and UNPIVOT in less than an hour. Honestly, explaining MERGE is probably easier, and technically, it's trickier, with a number of potential "gotchas".
As maintaining my higher contract pay rate usually depends on my ability to provide code that is maintainable by existing personnel, I have a strong incentive to be sure that not only does it work and perform well, but that it can be easily modified when requirements change, without my services turning into a luxury item as a result. This often results in my learning all kinds of new and interesting ways to solve a problem. Thus I tend to stick with that kind of problem-solving methodology... break it down into the most basic elements, and assemble number 5 ! :hehe:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 8:59 am
Do they really need to understand how it works in order to use it? I think it’s sufficient to know what it’s used for – “Pivot one or more columns of data (of unknown row count) into a single xml value. Commonly used to generate a delimited list” – and how to use it.
Most folks don’t know how most of the TSQL native functions work under the hood but trust them to perform as per their entry in BOL.
I like the gist of what you’re saying, though, and I’m usually very much in favour of using a long but understandable query in favour of a shorter but difficult query, so long as there’s no significant performance penalty.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 30, 2014 at 9:18 am
ChrisM@Work (5/30/2014)
Do they really need to understand how it works in order to use it? I think it’s sufficient to know what it’s used for – “Pivot one or more columns of data (of unknown row count) into a single xml value. Commonly used to generate a delimited list” – and how to use it.Most folks don’t know how most of the TSQL native functions work under the hood but trust them to perform as per their entry in BOL.
I like the gist of what you’re saying, though, and I’m usually very much in favour of using a long but understandable query in favour of a shorter but difficult query, so long as there’s no significant performance penalty.
Yes, actually, they DO need to understand it. I can't tell you how often I come across code that has been modified and now doesn't work, and I have to go in and "fix it" so that their modifications actually do what they wanted them to do. It's the most common task I run into. Thus I now ensure that they understand WHAT it does, and not necessarily the HOW that SQL Server uses to achieve it. Most importantly, I ensure they know how they can make changes, and what the dependencies are for making such changes that will still work. IOW, I teach them how to break it... and how to recognize that they broke it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 3:48 pm
I think the overhead of XML is unwarranted here, if I understand the desired output correctly. needed[/i].]
But, for the current situation, why not just something like this:
SELECT
tmask,
STUFF(
CASE WHEN tmask & 1 > 0 THEN ' 1' ELSE '' END +
CASE WHEN tmask & 2 > 0 THEN ' 2' ELSE '' END +
CASE WHEN tmask & 4 > 0 THEN ' 3' ELSE '' END +
CASE WHEN tmask & 8 > 0 THEN ' 4' ELSE '' END +
CASE WHEN tmask & 16 > 0 THEN ' 5' ELSE '' END +
CASE WHEN tmask & 32 > 0 THEN ' 6' ELSE '' END +
CASE WHEN tmask & 64 > 0 THEN ' 7' ELSE '' END +
CASE WHEN tmask & 128 > 0 THEN ' 8' ELSE '' END +
CASE WHEN tmask & 256 > 0 THEN ' 9' ELSE '' END +
CASE WHEN tmask & 512 > 0 THEN ' 10' ELSE '' END +
CASE WHEN tmask & 1024 > 0 THEN ' 11' ELSE '' END +
CASE WHEN tmask & 2048 > 0 THEN ' 12' ELSE '' END
, 1, 1, '') AS Fields
FROM (
SELECT CAST(18 AS int) AS tmask UNION ALL
SELECT 255
) AS test_data
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".
May 30, 2014 at 8:30 pm
sgmunson (5/29/2014)
The string it returns tells you which bits would be set to 1 in the binary equivalent of the input number (tMask, presumably coming from the table). When I supply multiple records in the position of the existing table, I always get the binary value of the largest value among those records.
If you'll pardon me for saying so, ol' friend, that's not quite right. In fact, if everyone will pardon me, no one that has posted so far has gotten it right.
First, none of the solutions (IMHO) handles it right according to the desired output when there are no bits set. Instead of returning an empty string, they all return NULL. Of course, like I said, that's just my opinion based on the limited information given for this problem but the original code does, in fact, return an empty string if there are no bits set.
That's not the half of it though. Everyone one overlooked the fact that there's a MAX on each case. The original code isn't just a bit slicer... it's also a "Vertical OR" that detects not what the maximum value of a set of rows is, but whether ANY of the rows have a bit set in a particular position. Let's see what I mean. For simplicity sake, we'll use just one byte...
Let's use all of the powers of 2 for bits 0 through 7. To draw a simple picture of it all, we'll have 8 numbers and each number will set 1 and only one bit.
===== BIT =====
Value 7 6 5 4 3 2 1 0
===== ===============
1 0 0 0 0 0 0 0 1
2 0 0 0 0 0 0 1 0
4 0 0 0 0 0 1 0 0
8 0 0 0 0 1 0 0 0
16 0 0 0 1 0 0 0 0
32 0 0 1 0 0 0 0 0
64 0 1 0 0 0 0 0 0
128 1 0 0 0 0 0 0 0
I think you all agree that the largest value in the diagram above is only 128. Let's build a test table with those values in it and run the original code against it. I'm setting up the test table using "old" 2005 compatible code so that any one still on 2005 (or earlier) can still follow along.
SELECT t.tMask
INTO #MyHead
FROM (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 4 UNION ALL
SELECT 8 UNION ALL
SELECT 16 UNION ALL
SELECT 32 UNION ALL
SELECT 64 UNION ALL
SELECT 128
) t (tMask)
;
Now, except for the table name in the FROM clause, let's run the original code from the OP.
select
replace(rtrim(
case when max(case when b = 0 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '1 ' else '' end
+ case when max(case when b = 1 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '2 ' else '' end
+ case when max(case when b = 2 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '3 ' else '' end
+ case when max(case when b = 3 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '4 ' else '' end
+ case when max(case when b = 4 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '5 ' else '' end
+ case when max(case when b = 5 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '6 ' else '' end
+ case when max(case when b = 6 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '7 ' else '' end
+ case when max(case when b = 7 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '8 ' else '' end
+ case when max(case when b = 8 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '9 ' else '' end
+ case when max(case when b = 9 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '10 ' else '' end
+ case when max(case when b = 10 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '11 ' else '' end
+ case when max(case when b = 11 then convert(varchar(2), ((convert(smallint, convert(binary(2), tMask)) & e)) / e) else 0 end ) = 1 then '12 ' else '' end
),' ',',') as [tMask]
FROM #MyHead
join (
select 11 as b,2048 as e union all select 10, 1024 union all select 9, 512 union all select 8, 256
union all select 7, 128 union all select 6, 64 union all select 5, 32 union all select 4, 16
union all select 3, 8 union all select 2, 4 union all select 1, 2 union all select 0, 1
) bits ON 1=1
;
... and that returns the following...
tMask
-------------------
1,2,3,4,5,6,7,8
(1 row(s) affected)
The largest number in our data was, in fact, just 128 but the original code returns all 8 bits being set (the number 255, if anyone is the least bit {no pun intended} unsure).
So, not only is it slicing bit-wise, it's also saying that if any of the numbers have a bit set in a given position, then return a 1 for that position. In shorter terms, it's doing a vertical OR. It was never designed to return more than 1 row nor was it designed to return the bit pattern of the maximum value in the row set.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply