3522465 - Story displays an error "Internal Error Occurred" - Attribute value is not a number

• Story displays an error "Internal Error Occurred", upon building a calculated column using TONUMBER formula.
• The error message displays "Internal error occurred while processing", when previewing the query.
• Detailed error, in Browser > Network log, displays "Caught exception : exception 42111: SQL Error : [6930] attribute value is not a number; exception 70006930: attribute value is not a number"
• "attribute value is not a date or wrong syntax"
  

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.  

• SAP SuccessFactors HCM Suite
  • Analytics & Reporting (Ad Hoc, YouCalc, ORD)
    • Story reports
      • Query Designer - Filter, Calculated Column, Params 

1. Create a new query (or) Edit an existing query.
2. Create a calculated column.
3. Use 'TONUMBER' formula to change a number string (number stored in a column of type String) field to number data type.
   • Eg: TONUMBER('User ID')
4. Validate the formula, and it shows 'Valid Formula'
5. Click OK.
6. When previewing the query, an error message "Internal error occurred while processing", is displayed
   
7. Notice the error in the Browser's network log (This KBA will help you with fetching this information 2280022):

1. 'Type: 2, Number: 42014, Text: "Internal error occurred while processing.",…}
2. DetailedError: "Caught exception : exception 42111: SQL Error : [6930] attribute value is not a number; exception 70006930: attribute value is not a number...'

There are two known causes of this error:

• One or more of the results in the column used in the formula has a string value that is not a number (contains text). For example User ID contains data like 10000000, 10000001, but also sfadmin (causing the issue).
• For Japanese locale, the string field includes some full-width values. For example, the number can be written as full-width or half-width in Japanese. However full-width number string values can not be recognized as valid number string by the system.

For the first cause, please validate by exporting all data from the affected column, and you will find a few values that are text values in the results. If they are identified, there are number of ways to avoid the error:

• Remove the string value in the source of the data (directly in the module)
• Filter it out from the query
• Modify the formula in the calculated column so it does not pick up the string value causing the issue
  Example: User ID has values like 10000000, 10000001, 10000002, sfadmin
  TONUMBER (IF ([User ID] = "sfadmin", "0", [User ID]))

If the string contains any null or empty values, the calculated column logic should be adjusted as shown below.

IF(
  (IF(ISNULL(FIELD_NAME),
                1,
                0
        )=1)  OR FIELD_NAME = "N/A" OR LENGTH(FIELD_NAME) = 0,
  0,
  TONUMBER((FIELD_NAME))
)

For the second cause, you can refer to the above, or alternatively, maintain the number string as half-width in the source.

3462894 - Story Report "42111: SQL Error : div by zero occurred error returned from /\n" error while preview query with calculated column

Internal error occurred while processing, TONUMBER, valid number string, full-width, half-width, 'Exception 70006930: attribute value is not a number' formula calculated column Story text, attribute value is not a date or wrong syntax, TODATE , KBA , LOD-SF-ANA-SAC-QDS , Query Designer - Filter, Calculated Column, Params , LOD-SF-ANA-SAC , Stories in People Analytics , Problem