cancel
Showing results for 
Search instead for 
Did you mean: 

Stuck up with Formatted Search. Help needed!

Former Member
0 Kudos

Hello All,

As a part of customization, I wrote a FMS on the Unit Price of the Sales Order field but I am getting data type conversion error. Since the client is of Chemical Industry, their calculations work in the reverse way and I had to write this FMS on the Unit Price.

Here is the FMS i have written:

Declare @Total numeric (19,6)

Declare @Unit numeric (19,6)

Declare @Price numeric (19,6)

Set @Total = (Select Case when $[$38.21.0] = 0 then 1 else $[$38.21.0] end)

Set @Unit = (Select Case when $[$38.11.0] = 0 then 1 else $[$38.11.0] end)

Set @Price = (@Total/@Unit)

Select @Price

Since The LineTotal and Quantity fields are defined as numeric(19,6), I defined the vriables also as Numeric(19,6).

a) When I try to run the FMS, I am getting an error "Conversion failed when converting nvarchar value to int".

b) When I replace .0 with .NUMBER, the FMS is calculating wrongly. Why?

Where is the problem with the FMS?

Edited by: Bharath Kumar on Nov 26, 2010 10:05 AM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this:


IF $[$38.11.number] != 0
Select $[$38.21.number] / $[$38.11.number]

Former Member
0 Kudos

Hi Gordon Du,

Thanks for the query. It worked well and the issue is resolved.

Regards,

Bharath S

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

What is wrong when you gave .number that should be correct? Give an example.

Thanks,

Gordon

Former Member
0 Kudos

Thanks Gordon Du,

When I give .NUMBER instead of .0, the system is not populating the value automatically as it used to do when I use .0. Moreover, when I manually press Shift + F2 in that particular field, the value is populated with a different value than what it should be.

Regards,

Bharath S

Former Member
0 Kudos

Hi Bharath...........

Declare @Total Float
Declare @Unit Float
Declare @Price Float
Declare @var1 as Float
Declare @var2 as Float
Declare @var3 as Float
Declare @var4 as Float
Set @Total=$[$38.21.0]
Set @Unit=$[$38.11.0]
Set @var1=cast( substring(replace(@Total,',',''),0,len(replace(@Total,',',''))-1) as float)
Set @var2=cast( substring(replace(@Unit,',',''),0,len(replace(@Unit,',',''))-1) as float)
Set @var3=(Select Case when @var1 = 0 then 1 else @var1 end)
Set @var4=(Select Case when @var2 = 0 then 1 else @var2 end)
Set @Price = (@var3/@var4)

Select @Price

Try above........

Regards,

Rahul

Former Member
0 Kudos

Hi Rahul,

Thanks for the quick help. I am still getting the data conversion error.

Regards,

Bharath S