Error converting data type nvarchar to numeric.

[#1]

Hi Sap Experts

Please can you advise if there is a formula to convert the data type from nvarchar to numeric.

I have raised a formatted search but when the quantity is separated by a comma at thousand I get the below error message.  My query is as per the below

'Error converting data type nvarchar to numeric.'

SELECT (T0.IWeight1 * $[$-38.11]) FROM OITM T0 WHERE $[$-38.1]=t0.itemcode

Many Thanks

Chris

[Johan_Hakkesteegt · Accepted solution]

Hi Chris,

First you need to make sure that the conversion error is due to the thousands separator, and not due to an empty field.

Then you can try to use a CASE statement. Something like this:

SELECT (T0.IWeight1 *

CASE

WHEN CAST($[$-38.11] AS NVARCHAR) LIKE '%.%' THEN REPLACE(CAST($[$-38.11] AS NVARCHAR), '.', '')

ELSE $[$-38.11] END)

FROM OITM T0 WHERE $[$-38.1]=t0.itemcode

Regards,

Johan

[chris_fawcett · Accepted solution]

No Sorry Johan it is still not working.  The query now multiplies the results by 1000000

I have divided by this amount to get the desired results but I still get the below error message when the quantity is 1,000 or above?

Please see below error message

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type nvarchar to numeric.  '' (SWEI)

SELECT ((T0.IWeight1 *

CASE

WHEN CAST($[$-38.11] AS NVARCHAR) LIKE '%.%' THEN REPLACE(CAST($[$-38.11] AS NVARCHAR), '.', '')

ELSE $[$-38.11] END))/1000000

FROM OITM T0 WHERE $[$-38.1]=t0.itemcode

Regards

Chris

[Johan_Hakkesteegt · Accepted solution]

Hi Chris,

Let's see what values the query is trying to work with.

Please open the form the FMS is in, and go to the last entry (so the form is populated with data), then go to Tools > Queries > User Queries > the query group you saved the query in and execute the query directly. Now take a look at the query text. Instead of $[$-38.11]  you should now see a number, a text, an empty string, or something like that.

Regards,

Johan

[chris_fawcett · Accepted solution]

Hi Johan

Please see below

SELECT ((T0.IWeight1 *

CASE

WHEN CAST(N'10.000000' AS NVARCHAR) LIKE '%.%' THEN REPLACE(CAST(N'10.000000' AS NVARCHAR), '.', '')

ELSE N'10.000000' END))/1000000

FROM OITM T0 WHERE N'DS0384              '=t0.itemcode

And a result of 100.00

Regards

Chris

[Johan_Hakkesteegt · Accepted solution]

Hi Chris,

Looking at it, I realize that we are replacing the wrong character, and not replacing a character.

Please give this a try:

SELECT (T0.IWeight1 *

CASE

  WHEN CAST($[$-38.11] AS NVARCHAR) LIKE '%,%' THEN REPLACE(REPLACE(CAST($[$-38.11] AS NVARCHAR), ',', ''), '.', ',')

  ELSE REPLACE(CAST($[$-38.11] AS NVARCHAR), '.', ',')

END)

FROM OITM T0 WHERE $[$-38.1]=t0.itemcode

Regards,

Johan

[chris_fawcett · Accepted solution]

Johan

No Sorry, I just get the below error message even when the quantity does not have a thousand separator now?

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type nvarchar to numeric.  'Document' (RDOC)

I have looked at the general setting in SAP B1 and there is an option to exclude the thousand separator. But I am not sure how this will effect current documents and would rather leave the setting how it is and edit the query if possible.

Regards

Chris

[Johan_Hakkesteegt · Accepted solution]

Hi Chris,

Converting a numeric value to data type money has helped me in the past to eliminate the decimal separator problem.

Please try this:

SELECT (T0.IWeight1 * CAST($[$-38.11] AS MONEY))

FROM OITM T0 WHERE $[$-38.1]=t0.itemcode

or

SELECT CAST((T0.IWeight1 * CAST($[$-38.11] AS MONEY)) AS MONEY)

FROM OITM T0 WHERE $[$-38.1]=t0.itemcode

Regards,

Johan

[chris_fawcett · Accepted solution]

Yes, It works 🙂  ... Magic!

Thanks for you help  🙂

[Former Member]

Hello i am facing the same error at below stored procedure

IF @transaction_type = 'A' AND @object_type = '13' BEGIN DECLARE @FreInv NVARCHAR SET @FreInv = (SELECT ((((T1.DocTotal+T1.DiscSum)-T1.RoundDif) *.7/100) + T1.RoundDif) FROM OINV T1 WHERE T1.DocEntry=@list_of_cols_val_tab_del ) If exists (SELECT T0.DocEntry FROM OINV T0 Inner Join INV1 T2 On T0.DocEntry=T2.DocEntry WHERE @FreInv < 0.00 AND T0.DocEntry=@list_of_cols_val_tab_del) Begin SET @error = 122 SET @error_message = 'Test Error' End End

[chris_fawcett]

Hi Nagarajan

Sorry I get the same error message. 

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type nvarchar to numeric.  '' (SWEI)

As mentioned the fms works when the quantity dose not have a thousand separator?  

Regards

Chris

[kothandaraman_nagarajan]

Hi,

Please share your item master date with UDF fields.

Thanks

[kothandaraman_nagarajan]

Hi,

Please try this,

SELECT (T0.IWeight1 * $[$-38.11.0]) FROM OITM T0 WHERE $[$-38.1.0]=t0.itemcode

Thanks