Query

[Former Member]

hi all,

i am using this query to convert inches to cm

Declare @a as nvarchar
Declare @b as nvarchar
Declare @c as nvarchar
Declare @d as nvarchar
Declare @e as nvarchar
Declare @f as nvarchar
Declare @g as nvarchar
Declare @h as nvarchar
set @a=convert(Numeric(10),(($[@SIZETABLE.U_LEN.number])/2.54))
set @b=convert(Numeric(10),(($[@SIZETABLE.U_width.number])/2.54))
set @c=convert(Numeric(10),(($[@SIZETABLE.U_hgt.number])/2.54))
set @d=convert(Numeric(10),(($[@SIZETABLE.U_dia.number])/2.54))
set @e=convert(Numeric(10),(($[@SIZETABLE.U_thick.number])/2.54))
set @f=convert(Numeric(10),(($[@SIZETABLE.U_bag.number])/2.54))
set @g=convert(Numeric(10),(($[@SIZETABLE.U_garment.number])/2.54))
set @h=convert(Numeric(10),(($[@SIZETABLE.U_size.number])/2.54))
SELECT  case when @a='' then '' else @a+'L' 
end + case when @b='0' then '' else 'x'+ @b
+'w' end+case when @c ='0' then '' else 'x'+@c
+'h' end+case when @d='0' then '' else 'x'+@d+
'd' end+case when @e='0' then '' else 'x'+@e+
't' end+case when @f='0' then '' else 'x'+@f+'b' end+
' '+rtrim($[@sizetable.U_unit]) + case when @g='0' then '' else ','+
@g+'g' end+case when @h='0' then '' else ','+
@h+'s' end

this query is working fine for numeric(10) but i want result upto 2 decimal place when i am convert it to numeric(10,2) it gives me error 'Arithmetic overflow error converting nvarchar to data type numeric'. plz suggest me.

thanks

Rahul

[former_member204969]

Try to change the expression @a='' to @a='0' ' !

[Former Member]

hi István,

i tried it but same error

[Former Member]

all the fields in sap is nvarchar

[Former Member]

hi rahul sagar,

try to change convert(Numeric(10), ...) to Convert(Nvarchar(100),....)

thanks

H2

[Former Member]

hi H2,

i use convert(nvarchar(38,2),@a)

it gives me error "CAST or CONVERT: invalid attributes specified for type 'nvarchar'"

[Former Member]

Hi H2,

sorry for the last post it was wrongly posted

Convert(nvarchar(100),@a) is working but i want result in 2 decimal place but it returning me integer ex.

10/2.54 = 3.937

this query return 3

i want 3.94

[Former Member]

hi rahul sagar

you can try:

set @a=convert(Nvarchar(100),  CONVERT(FLOAT, $[@SIZETABLE.U_LEN.number] /2.54 ) )

you result was rounded with 0 decimal because type of U_LEN feild is Interger, so the division as it will automatically convert to int.

thanks

H2

Edited by: Hoe Pham Huy on Jun 1, 2011 4:29 PM

[Former Member]

hi H2,

this query return same value 3

and type of U_LEN feild is nvarchar in sap

[Former Member]

solved by using nvarchar(10) in declare