# Arithmetic overflow during explicit conversion of NUMERIC value '2147483648' to a INT field .

Hi,

I'm trying to perform the below

SELECT UD.id_user, #POT.n
FROM    USER_DESK UD
,            #POT
WHERE convert(int,UD.id_flg_desk) & #POT.n = #POT.n

but I get

>[Error] Script lines: 1-5 --------------------------
Arithmetic overflow during explicit conversion of NUMERIC value '2147483648' to a INT field .

Clearly the int type is too small. What do I need to do to fix this. What data type should I use, stc. Thanks
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
try this

convert(decimal(18,0),UD.id_flg_desk)
0
Commented:
or

convert(numeric(18),UD.id_flg_desk)

where 18 is the number of digits you want
0
Author Commented:
OK, In now have

SELECT UD.id_user, #POT.n
FROM    USER_DESK UD
,            #POT
WHERE convert(numeric(21),UD.id_flg_desk) & #POT.n = #POT.n

>[Error] Script lines: 1-5 --------------------------
Invalid operator for datatype op: BOOLEAN AND type: NUMERIC.
Msg: 403, Level: 16, State: 1
0
Commented:
That convert expression must be equal to something, otherwise Sybase is gonna think it's a boolean.

SELECT UD.id_user, #POT.n
FROM    USER_DESK UD
,            #POT
WHERE convert(numeric(21),UD.id_flg_desk) = SOMEVALUE & #POT.n = #POT.n
0
Principal ConsultantCommented:
Not being able to use numerics for boolean ops means you'll have to expand this to cope with a second int field. That will get a bit tricky and I'll be able to think about this better after some sleep (fresh off the plane from the US, and that's a long trip). 8-)
0
Commented:
To use bitwise operators datatype must be integer type. You can use bigint instead of int:

SELECT UD.id_user, #POT.n
FROM    USER_DESK UD
,            #POT
WHERE convert(bigint,UD.id_flg_desk) & #POT.n = #POT.n

Note: I hope you are using an ASE 15
0

Experts Exchange Solution brought to you by