Post reply

bitwise identity for static lookup table???

  • 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…

  • October 7, 2004 at 8:10 am

    #525759

    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 2n-1

    LinkedIn Profile

  • October 7, 2004 at 8:17 am

    #525762

    Cheers...

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

    Do you have any ideas?

  • October 7, 2004 at 8:52 am

    #525770

    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

  • October 7, 2004 at 8:55 am

    #525771

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

    LinkedIn Profile

  • October 7, 2004 at 9:32 am

    #525784

    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...

  • October 8, 2004 at 6:34 am

    #525930

    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".

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

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