| Author |
Topic |
|
cckelly
Starting Member
9 Posts |
 Posted - 2008-02-07 : 13:15:27
|
| This description is a little lengthy; but, my hope is that people who try to help me will have all the info they need. I've googled, tried to find white papers about "bitwise functions", et cetera, but I haven't found anything that really explains the inner workings well enough for me to devise a solution. For a particular project, I need help from someone who really understands Microsoft SQL Server 7. (We are slowly migrating all our servers to 2000, so I need to find a solution that will work on both versions.)There are many columns in "the table"; but, for this example, they really don't matter.There is a column that functions as the primary key. There are 15 individual columns (data_type "bit", size 1, default 0 [zero]) in this table. They are used as "flags." The off-the-shelf application that maintains this table does not edit these flags appropriately. Therefore, the database is full of records that have an inappropriate number of flags "set" (to a value of 1). The rules are: it is VALID for 1 or 2 of the 15 flags to be set; it is INVALID for none of the 15 flags to be set; it is INVALID for more than 2 of the 15 flags to be set. I want to write a query that lists the primary key of each record that does not have an appropriate number of flags set. This list will be used to produce a "go fix these errors" edit report. Instead of writing a CASE statement to handle all the combinations of the 15 flags, I thought perhaps I could "concatenate" all 15 flags to create a single binary field or text string. Then, I could evaluate the "created binary field" with some kind of bitwise function (AND or XOR?). Or, if the text-string method is better, I could count the number of 1's in the "created text field."Does anyone have any suggestions? I have experience with "programming languages" where I could create an array and loop through them, etc. But, in SQL, I need help.I don't know how to concatenate the 15 binary-bit columns to create a SINGLE field. (Could I accomplish the task by using some pre-defined function, or a really-complicated formula?) AND, after I have this "single field" what do I do with it to determine whether the correct number of flags have been set? (I'm pretty sure that will be a portion of my WHERE clause.)Please help! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-07 : 13:20:23
|
| To return all errors, just convert each bit to an integer 1 or 0, add those integers up, and retrieve rows where that total is not between 1 and 2.select idfrom yourtablewhere cast(bit1 as int) + cast(bit2 as int) + .. etc not between 1 and 2that's it!I strongly recommend never using bitmasked columns in sql server, rarely have ever seen a need for it, it just over-complicates things and makes everything slower and more confusing.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-07 : 13:22:17
|
| SELECT PK FROM TABLEWHERE CAST(f1 as int) + CAST(f2 as int)+....+CAST(f15 as int) >2--moethan 2 flags setOR CAST(f1 as int) + CAST(f2 as int)+....+CAST(f15 as int) =0--no flags setall other cases will filter out (1 falg set,2 flag set)Hope this is what you want |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-07 : 13:23:28
|
| and strongly think about migratin to sql server 2005 and not 200 since support for 2k is at the end soon._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
cckelly
Starting Member
9 Posts |
Posted - 2008-02-07 : 14:08:46
|
[quote]Originally posted by spirit1
and strongly think about migratin to sql server 2005 and not 200 since support for 2k is at the end soon._______________________________________________Hello, everyone!Thanks for the replies. I'm going to run a few tests this afternoon and will let you know how it goes.Regarding the version suggestion:Sadly, I can't choose the version we use for the upgrade.Our off-the-shelf software company is a little behind-the-times, so we're at their mercy. Apparently, our Support agreement gives the software company complete control over "our" server--we're not supposed to create "views" or create "procedures" or modify the schema in any way. AMAZING, ISN'T IT ?!?! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-07 : 14:20:49
|
indeed... that's some competent management you have there  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
cckelly
Starting Member
9 Posts |
Posted - 2008-02-07 : 15:07:06
|
| I tested the code that user "visakh16" suggested. It works perfectly! THANK YOU SO VERY MUCH !(And I really appreciate the explanations that you included !!) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-02-07 : 15:14:27
|
uh .. he didn't explain anything, and he posted the same solution I did, which I did explain .....  - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-07 : 23:43:53
|
quote:
Originally posted by jsmith8858
uh .. he didn't explain anything, and he posted the same solution I did, which I did explain ..... - Jeffhttp://weblogs.sqlteam.com/JeffS
I had no intention of repeating the same solution. Unfortunately, i didnt refresh before i posted. Thats why this duplication. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-08 : 07:12:33
|
quote:
Originally posted by jsmith8858
Oh, I know no problem there...just that I am the one who explained it...- Jeffhttp://weblogs.sqlteam.com/JeffS
Cheers buddy |
|
|
|
|
need help with "bit" columns in SQL