bitwise identity for static lookup table???

Question

Post reply

bitwise identity for static lookup table???

Andy Butson

SSC Veteran

Points: 298
More actions
October 7, 2004 at 7:49 am

#66925

I would like to create an identity on a static lookup table that can be used in bitwise operators. The static table will be used in a bitwise lookup to see if the particular record has an error against it. The main problem is that each record could have multiple errors.

Example Valid
0 = Item OK
1 = Issue1
2 = Issue2
4 = Issue3
8 = Issue4
16 = Issue5
32 = Issue6

For example a record with a value of 35 would have Issue1, Issue4 and Issue5.

1. I really don’t want to introduce a new table to store this information.
2. I don't want the administrator to have to calculate the next identity value each time a new value is added

Thanks for the help…

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76000 More actions · Answer 1

This design limits the number of possible values to:

31 if the Int field is used.
63 if the BigInt field is used.

If you are happy with that then why not prepopulate the table. Issues are only 2^n-1

LinkedIn Profile

Andy Butson SSC Veteran Points: 298 More actions · Answer 2

Cheers...

But the size of the field isn't an issue the real question is creating the identity with LastIdentity².

Do you have any ideas?

David.Poole SSC Guru Points: 76000 More actions · Answer 3

DECLARE @tbl TABLE(Id Int , Issue CHAR(8))

DECLARE @byLoop TinyInt

SET @byLoop=1

WHILE @byLoop<32

BEGIN

INSERT @tbl(Id , Issue)

SELECT POWER(2,@byLoop-1),'Issue ' + CAST(@byLoop AS VARCHAR(2))

SET @byLoop=@byLoop+1

END

SELECT * from @tbl

LinkedIn Profile

David.Poole SSC Guru Points: 76000 More actions · Answer 4

Note POWER will give an arithmetic error if you exceed 31 issues because POWER can only return a 32 bit integer.

LinkedIn Profile

Andy Butson SSC Veteran Points: 298 More actions · Answer 5

Thanks!

I think i just need to plug that into a udf and call it from the item default.

Sorry it the first time i've posted a question do i have to give you points???

Once again thanks a lot...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

No there's no point involved in this forum... except maybe the number of postings we do and how well we performe in the Question of the day "contest".