another way is like we need to check the datatypes is in sequance or not. This will result in the erroring fields, simply returning 0, and then you can focus in on which records are your problems. Why ORA-01722 error will come This is another common error the SQL and PL SQL developers and DBAs will face.This error will come in oracle for mismatching the datatype as number to string or trying to insert the string value in to number values.There should be different reasons for this error.I will mention some scenarios in which this error will come. ORA-01722: Invalid number Common cause Date formatting error For example, re-date the date on a date that is already in string format. As per my understanding, Generally, invalid number will occurs when there is no sequence match between sql override query and the port we created in source qualifier query. The Val function will not fail, because it is not converting, it is simply taking any number values it can find from the PROCESS_LEVEL table field. Hence the invalid number error will be thrown. In the following query, we are trying to Sum the name of the customers which is not possible. The item effective date of the select matches up to the item effective date. Then the Int function fails because it can’t convert the TEXT to a NUMBERĭistrict_ProcLvl: Val(Right(,3)) Invalid number error occurs during the usage of the aggregate functions such as Sum() when the non-numeric columns are passed to these types of aggregate functions. Im getting the ORA-01722: invalid number error and I am not sure what is wrong.
The PROCESS_LEVEL table field has data like X0123, but perhaps one or two of the fields have ONLY text in them, like TEXTDATA
For information about this error, please refer to SAP Knowledge Base Article. The PROCESS_LEVEL field has data like X0123 Error The following database error occurred: ORA-01722: invalid number. You are likely doing a type conversion (from text type to number type) on a database field that has actual text in it, instead of numbers.ĭistrict_ProcLvl: Int(Right(,3))