asked on

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

ee_rlee

try this

convert(decimal(18,0),UD.id_flg_desk)

brunoguimaraes

or

convert(numeric(18),UD.id_flg_desk)

where 18 is the number of digits you want

bowemc

ASKER

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

brunoguimaraes

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

Joe Woodhouse

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

ASKER CERTIFIED SOLUTION

alpmoon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial