Re: Veryfing decimal fields... what is the best solution?

Hi,

I assume you created the physical files with DDS. In this way invalid numeric 
values can be inserted.
If you create your physical files with SQL instead, it is not possible to enter 
invalid numeric values.
There are some architectural differences between DDS described physical files 
and SQL defined tables. The major difference is, in DDS described tables data 
validation occurs when the record is read. In SQL defined tables on the other 
hand data validation happens when a row gets written.

If you don't believe just try the following.
Create a physical file with CRTPF and fixed length and enter some rows (with 
invalid numeric values).
Define a DDS described physical file with numeric fields and try to copy the 
first file into the DDS described file.
(CPYF with *NOCHK). All rows will be inserted.
Now create a table with the same structure but with SQL and try to copy the 
first table (not the DDS described file) with Copy *NOCHK into the SQL 
described table. Now you'll detect, that even though you specified *NOCHK, an 
error occured and only the rows until the first wrong record are inserted.

To check the numeric fields that hold invalid numeric values, you may use a 
work around in SQL. 
The following snippet returns 1 if invalid numeric values are found in the 
numeric field and otherwise 0.
select  Case When Length(Trim(Translate(Hex(MyNumFld),
                                        ' ', '1234567890FD'))) = 0              
    
             Then 1 Else 0 End       
from MyField  

Birgitta                                      

"Charles St-Laurent" <dringpiece@xxxxxxxxxxx> schrieb im Newsbeitrag 
news:<ehakbv$i1i$1@xxxxxxxxxxxxx>...

Hi!

We are converting S/36 files to native files with external descriptions. We
want to verify that each numeric field of each record contains valid values.
If a numeric field suffers from a decimal data error, we want to know the
field name that contains an incorrect value and the RRN.

With a SQL query, it seems that there is no simple way to test numeric
fields. I cannot use FREE RPG in this project and our OS level is V4R5. But
I can use SQLRPGLE if necessary.

Any suggestion?

Charles



-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.