We have a file that contains an alpha customer number (required because of EDI standards). The customer number in our application software is 9,0. Is there a way to create a logical view over either the EDI file, or our application files, that will convert the alpha field to a numeric field in the logical? You can create a logical file that just maps the field to a number field data type. If COL1 was the alpha customer number field...
in the physical file, the Logical file DDS would just define a new data type for COL1.
COL1 9S 0
SQL Views can also be used. SQL Views are equivalent to non-keyed logical file and actually can be opened as a non-keyed logical file by HLL applications. SQL Views offer superior functions and expression capabilities that logical files. The following SQL View could be created directly over the EDI file. This example assumes that the EDI file is stored as one large character column in an iSeries file & that the customer number is the first nine bytes of the EDI data record. create view v1 (custnum) as (select decimal(substr(c1,1,9),9,0) from edifile)
MORE INFORMATION ON THIS TOPIC
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: tips, tutorials and more.
Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.
Dig deeper on Oracle on iSeries
Related Q&A from Kent Milligan
To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output ...continue reading
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.