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_member206488 · Accepted solution]

Hi Rahul,

try this:

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(nvarchar(50),(($[@SIZETABLE.U_LEN.number])/2.54))
set @b=convert(nvarchar(50),(($[@SIZETABLE.U_width.number])/2.54))
set @c=convert(nvarchar(50),(($[@SIZETABLE.U_hgt.number])/2.54))
set @d=convert(nvarchar(50),(($[@SIZETABLE.U_dia.number])/2.54))
set @e=convert(nvarchar(50),(($[@SIZETABLE.U_thick.number])/2.54))
set @f=convert(nvarchar(50),(($[@SIZETABLE.U_bag.number])/2.54))
set @g=convert(nvarchar(50),(($[@SIZETABLE.U_garment.number])/2.54))
set @h=convert(nvarchar(50),(($[@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

Thanks,

Neetu

[Former Member · Accepted solution]

hi neetu,

@a=convert(nvarchar(50),(($[@SIZETABLE.U_LEN.number])/2.54))

if $[@SIZETABLE.U_LEN.number])=10 then it returns me 3

but i want 3.94

but original value is 10/2.54= 3.937

[former_member206488 · Accepted solution]

Hi Rahul,

Trythis:

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

Thanks,

Neetu

[Former Member · Accepted solution]

hi neetu,

this query giving error "Error converting data type varchar to float."

@manvender i already tried dec(10,2) it giving error "Arithmetic overflow error converting expression to data type nvarchar."

[Former Member · Accepted solution]

type of all the fields is nvarchar

[Former Member · Accepted solution]

Rahul Does not matter you can CAST or Convert your data type.

Thanks

Manvendra Singh Niranjan

[Former Member · Accepted solution]

Hello Rahul,

You should check your all data type lenght ,like Nvarchar(10) means your resulting value should be in 10 charcter range for proper result.

Thanks

Manvendra Singh Niranjan

[Former Member]

Hello Rahul,

Why are you not using Decimal(10,2) ?

Thanks

Manvendra Singh Niranjan