search cancel

Finding non numeric data

book

Article ID: 128904

calendar_today

Updated On:

Products

Datacom DATACOM - AD CIS COMMON SERVICES FOR Z/OS 90S SERVICES DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS COMMON PRODUCT SERVICES COMPONENT Common Services CA ECOMETER SERVER COMPONENT FOC EASYTRIEVE REPORT GENERATOR FOR COMMON SERVICES INFOCAI MAINTENANCE IPC UNICENTER JCLCHECK COMMON COMPONENT Mainframe VM Product Manager CHORUS SOFTWARE MANAGER CA ON DEMAND PORTAL CA Service Desk Manager - Unified Self Service PAM CLIENT FOR LINUX ON MAINFRAME MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME GRAPHICAL MANAGEMENT INTERFACE WEB ADMINISTRATOR FOR TOP SECRET Xpertware

Issue/Introduction



Is there anyway using Dataquery DQL or SQL to find if a numeric field contains non numeric data.

Environment

z/os or z/VSE, CA Datacom/DB

Resolution

You may do one of the following:

From DQL (Dataquery Language).
- Do a DISPLAY or PRINT command request on that table and find the output fields showing "***E1***".
        E1 : A numeric column named in a SET, PRINT, or DISPLAY command contains non-numeric data.
       So, a PRINT or DISPLAY will display error "E1" if the field does not contain numeric data.

- Do a DBUTLTY EXTRACT and via a COBOL program or other meaning identify the invalid non numeric  field.


From SQL
- Write a small SQL program using a DECLARE CURSOR/OPEN cursor/FETCH/CLOSE cursor. 
Declare the cursor for a select on this problem column CESS_CNT and all the columns from the master key. 
The FETCH fails on each row with invalid data in that column CESS_CNT with SQLCODE -161. 
So test on that SQLCODE and then display the values of the master key columns so that you know which row needs to be corrected. 
You test on that SQLCODE -161 during the FETCH , do the display and then FETCH the next row until all rows have been read. Then you close the cursor.