While querying file to locate bad data in file which have a num field for date in which i have data as '+++++++++++' in some records. I want to update these bad data with zero or want to convert to correct data if possible.
Announcement
Collapse
No announcement yet.
Bad data in file
Collapse
X
-
That's a little tricky, since SQL it is not possible to enter invalid numeric values in SQL Tables (contrary to DDS described physical files).
But you can do the following trick:- Run your SELECT * Statement with ACS (Access Client Solutions - Run SQL Script. And make sure all rows are read.
- Invalid numeric values cause an CPF5035 Joblog-Entry.
- You can search the joblog (for the current job) to get all CPF5035 messages with the following SQL-Statement.
Code:Select * from Table(Joblog_Info('*')) x Where Message_Id = 'CPF5035';
Code:Select Cast(Trim(Substr(Message_Tokens, 1, 10)) as VarChar(10)) File, Cast(Trim(Substr(Message_Tokens, 21, 10)) as VarChar(10)) Library, Cast(Trim(Substr(Message_Tokens, 31, 10)) as VarChar(10)) Field, BigInt(Substr(Hex(Message_Tokens), 81, 8)) RRN, Cast(Trim(Substr(Message_Second_Level_Text, 57)) as VarChar(256)) Text From Table(QSYS2.JobLog_Info('*')) x where MESSAGE_ID = 'CPF5035';
- Likes 1
Comment
-
@JonBoy: Sure you need a little program, but ... we just create a view over the SELECT statement and then loop through the view and update the appropriate columns with dynamic SQL
... and voilĂ you have a small tool which can revise any physical file.
... may be 20 statements
Comment
-
Maybe - but I've found that in these cases reporting is needed. I know we see these things differently but to me this kind of thing is in the category of "The answer is SQL ... now what was the problem". SQL is a fabulous tool - I use it all the time - but it wasn't designed for this kind of stuff and just feels forced to me.
-
-
Of course this kind of error requires a lot of analysis.
But I think that perhaps you can use the good old CPYF for this.
If you try to copy the file containing the invalid data to another temporary file
CPYF would react on the invalid data. You can specify that errors are allowed to happen and that the record should
be ignored and listed in character and HEX format in a spool file.
Then you can investigate what was wrong and correct it.
Repeating this, in the end no errors will occur and you have corrected all invalid data ---- at least into data in valid format
CPYF FROMFILE(*LIBL/myBadFile) TOFILE(QTEMP/XXX) MBROPT(*ADD) CRTFILE(*YES) OUTFMT(*HEX) PRINT(*ERROR) ERRLVL(*NOMAX)
Comment
Comment