FMS SUM HEADER FIELDS

[gonzalogomez]

Hello everybody.

I want to define a formatted search to add two fields in the order header, so that the total in the third field appears after exiting the second typed field.

I have defined 3 fields of type units and totals and structure amount.

SELECT $[ORDR.U_RTC_Principal] + $[ORDR.U_RTC_Intereses]

I want to get the total from the sum of the first 2, but if I use the + symbol (Sum), it concatenates them and if for example I use * (multiplication) it gives me an error "1).

SELECT $[ORDR.U_RTC_Principal] * $[ORDR.U_RTC_Intereses]

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand data type nvarchar is invalid for multiply operator.
2). [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement 'Alertasrecibidas' (OAIB) (s) could not be prepared.

[Felipe_Lima · Accepted solution]

Hi @gonzalogomez ,

The error "Operand data type nvarchar is invalid for multiply operator" occurs because SQL Server does not allow mathematical operations, such as multiplication, on string data types like nvarchar. To resolve this issue, you need to convert the nvarchar column to a numeric data type before performing the multiplication.

Example: SELECT CAST($[ORDR.U_RTC_Principal] AS FLOAT) + $[ORDR.U_RTC_Intereses]

I hope it helps.

*** Please, if this answer resolves your question, kindly consider marking it as accepted. This helps our community by highlighting useful solutions. Thank you! ***

BR,
Felipe

[gonzalogomez · Accepted solution]

Thank you. It works fine.