CA Datacom - DBCA DatacomCA Datacom - ADCA Datacom - ServerCA CISCA Common Services for z/OSCA 90s ServicesCA Database Management Solutions for DB2 for z/OSCA Common Product Services ComponentCA Common ServicesCA Datacom/ADCA ecoMeter Server Component FOCCA Easytrieve Report Generator for Common ServicesCA Infocai MaintenanceCA IPCUnicenter CA-JCLCheck Common ComponentCA Mainframe VM Product ManagerCA Chorus Software ManagerCA On Demand PortalCA Service Desk Manager - Unified Self ServiceCA PAM Client for Linux for zSeriesCA Mainframe Connector for Linux on System zCA Graphical Management InterfaceCA Web Administrator for Top SecretCA CA- 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.