This article describes some possibilities for dealing with data exceptions in IDMS/SQL.
The IDMS SQL Option is less tolerant of non-numeric data in numerically defined fields than network-defined databases are. When it encounters such data, a DB001025 DATA EXCEPTION abend is issued.
Release: All supported releases.
Component: SQL Option.
SELECT DEPT_HEAD_ID_0410 FROM EMPNET.DEPARTMENT WHERE DEPT_ID_0410=5300;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:
*+ 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
SELECT SUBSTR('*?%',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.
(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