If the SQL command in question is being executed in a CA-IDMS/Server environment, there are some settings which will allow the SQL engine to ignore the bad data and return a particular value.
See
Handling Invalid Numeric Data.
To set this attribute in an ODBC datasource, see
Setting Advanced Data Source Options.
To set it in a JDBC environment, use the InvalidDecimal value in the
Properties File Information and use the valid values specified in
Configuration Information.
The problem with those options is that the actual value can not be retrieved, only the predefined values of 0 or NULL can be returned to the application program.
Another option, in any environment, is to use the HEX() scalar function, which returns the internal hexadecimal value of a numeric field as a character string and will do so even if the passed parameter is a numeric field which has invalid data.
For example, assuming that the column in this example has an internal value of c'****', or x'5C5C5C5C', the following result is returned:
SELECT DEPT_HEAD_ID_0410 FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;
*+ Status = -4 SQLSTATE = 22005 Messages follow:
*+ DB001025 T70 C-4M322: Data exception
SELECT HEX(DEPT_HEAD_ID_0410) FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;
*+
*+ HEX(FUNCTION)
*+ -------------
*+ 5C5C5C5C
*+
*+ 1 row processed
Unfortunately there is no scalar function to do the reverse of HEX().
A user-written SQL function could be created, or something like this could be done if a subset of expected invalid values are anticipated:
SELECT SUBSTR('*?%',
(LOCATE(SUBSTR(HEX(DEPT_HEAD_ID_0410),1,2), '5C,6F,6C')+2)/3,1)
FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;
*+
*+ SUBSTR(FUNCTION)
*+ ----------------
*+ *
*+
*+ 1 row processed
The function call above will look at the first character in the DEPT_HEAD_ID_0410 field and return c'*', c'?' or c'%' depending on whether it is x'5C', x'6F' or x'6C' respectively.