Post reply

SQL and binary numbers

  • March 9, 2012 at 4:47 am

    #252241

    Im dealing with a our old mainframe that basically shoves everything into a sql database.

    i have a certain field that represents binary numbers sums them up and then stores that number in a decimal field. ie:

    Papersdue 1 ; Papers due

    Dueflag 2 ; Due letter sent flag

    Chaserflag 4 ; Chaser letter sent flag

    Feesettle 8 ; Fees settlement flag

    Statement 16 ; Statement flag

    Unconfirmed 32 ; Unconfirmed flag

    etc....

    so lets say a client has a value of 48, that means hes unconfirmed and statement.

    if they have 30 then they have statement, feesettle, chaser, and dueflag.

    im basically after everyone who has a unconfirmed flag.

    Any ideas?

  • March 9, 2012 at 4:56 am

    #1457076

    select * from tableName where (colname & 32 = 32)

  • March 9, 2012 at 5:03 am

    #1457079

    the field is stored as a decimal number which is coming back as '&' operator is incompatible with decimal and int types....

  • March 9, 2012 at 5:13 am

    #1457083

    I'm surprised it says it's incompatible with int. Have you tried CASTing or CONVERTing?

    John

  • March 9, 2012 at 5:19 am

    #1457086

    I have, if i convert 32 (which is decimal in the field) to varbinary i get this

    0x0C00000120000000

    then in the where clause i try convert(varbinary, C.X_stat) & 32 = 32 i get no results, there are definitely 32 in the x_stat field so i should get results

  • March 9, 2012 at 5:24 am

    #1457090

    looking at that link

    In a bitwise operation, only one expression can be of either binary or varbinary data type.

  • March 9, 2012 at 5:25 am

    #1457092

    Does that not make you curious to find out what results are being returned? Try this:

    SELECT convert(varbinary, C.X_stat) & 32

    FROM C

    John

  • March 9, 2012 at 5:29 am

    #1457097

    it does and it returns 0 for all rows, which confuses me some more! (not hard i guess)

  • March 9, 2012 at 5:43 am

    #1457108

    if do

    select convert(varbinary, C.X_stat)

    from C

    where C.X_Stat = 0

    it returns 0x0C00000100000000

    but if i do

    select convert(binary(10), 0)

    it returns 0x00000000000000000000

    I think the problem is the 1 in 9th digit, it always returns this 1 in that column when converting the X_Stat column.

  • March 9, 2012 at 5:48 am

    #1457112

    doesn't just converting it to an int work?

    select * from tableName where (convert(int,colname) & 32 = 32)

  • March 9, 2012 at 5:55 am

    #1457116

    Yes that works. must be down to the way i was trying to explicitly convert it to binary when the & would implicitly do that?

    if i do

    where C.X_stat & 32 = 32

    then i get: The data types decimal and int are incompatible in the '&' operator.

    but if i do

    where convert(int, C.X_Stat) & 32 = 32

    then it works fine.

  • March 9, 2012 at 6:08 am

    #1457125

    I'd save on the headache by changing the datatype in the table to INT. I might even add some computed columns to the table to split out the individual flags so I don't have to do the AND split in any code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 9, 2012 at 6:13 am

    #1457129

    I would if i could, but the tables are built by a program that was written from the manufacturs of the mainframe, each night it rebuilds the tables and then through the day if just refreshes the data within. Its a pain because i lose any triggers etc, which i have to rebuild with a job the next morning.

    its a nightmare because everything i do has to conform to the way they have built it.

    oh well spirit is strong again! 😀

  • March 9, 2012 at 7:01 am

    #1457166

    These are like Bits in a Byte being switched on or off

    AND ing with whatever (in this case 32) is the correct way

    However doing that seems to be a problem here

    However you are lucky here because any number greater than 31 is is unconfirmed because it is the higest number

    This will work as long as you do not have a 64 meaning They have gone bankrupt

  • March 9, 2012 at 9:12 am

    #1457257

    JamesX (3/9/2012)

    I would if i could, but the tables are built by a program that was written from the manufacturs of the mainframe, each night it rebuilds the tables and then through the day if just refreshes the data within. Its a pain because i lose any triggers etc, which i have to rebuild with a job the next morning.

    its a nightmare because everything i do has to conform to the way they have built it.

    oh well spirit is strong again! 😀

    as long as the table name is the same, just create a view that does the computed columns Jeff is suggesting, and change your code to always use the views to make it easy on yourself in the future.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 22 total)

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