Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: modulus and bigints

    SQL Server 2005:

    We are using one field to track many different side effects a patient may have using bitshifting. I need to do modulus 2 to decode the bigint field. The field is called bigChronic. Here's the piece of code to find if one of the side effects was chosen.
    Code:
    [code repeats]
    (case 
    when (bigChronic/65536) % 2 = 1 then 'Cognitive Problems,'
    else '' end
    )+
    [code repeats]
    further down the numbers get very large

    Code:
    +(case
    when (bigChronic/(8388608*262144)) % 2 = 1 then 'Bilateral Smile'
    else '' end
    )
    My understanding is that /, * and % can handle bigints and ints but something isn't working because I get this:

    Arithmetic overflow error converting expression to data type int.

    Why is it trying to convert to int at all? Doesn't bigint take precedence? Your help is much appreciated! I'm really starting to wish we had done separate fields for each one...

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chuckiej2
    I'm really starting to wish we had done separate fields for each one...
    No, you should have one table with patients, one table with side effects and one table with side effects per patient.

    Just stop that nonsense with bit shifting and make a proper relational design.

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Quote Originally Posted by shammat
    No, you should have one table with patients, one table with side effects and one table with side effects per patient.

    Just stop that nonsense with bit shifting and make a proper relational design.
    Obviously you are correct. My Excuse: the gentleman who developed it originally had cellphone databases on the brain when he did this.

    Now I have to complete this decoding so that I can bring it into a proper design as you describe.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The database engine assumes both your hard-coded values are ints, and tries to return the result as an int, which is not big enough to handle the output.

    Cast or convert one of the values to bigint explicitly:
    Code:
    select (8388608*convert(bigint, (262144))) % 2
    This tells SQL Server to allocate enough space for a bigint result.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •