RE: SQL Table Insert From Unformatted Data.

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

SQL does data validation on writes and if data doesn't match, it won't work.
That's probably why your INSERTs are failing.

You can try calling CPYF within SQL:

CALL QSYS/QCMDEXC('CPYF FROMFILE(sourceFile) TOFILE(ed971) CRTFILE(*NO)
FMTOPT(*MAP *DROP)', 0000000071.00000)

CPYF is powerful command with lots of options.

HTH

Elvis
 
-----Original Message-----
Subject: SQL Table Insert From Unformatted Data.

We have a file which our application uses to store reference information
which can be maintained by users rather than hard-coding in programs.
The file has a key and a generic field of 256 characters which can be
mapped into any format required.  Each key will have its own layout.

The mapping is achieved by having another file which holds the layout of
this field, with one record for every field defining it's start and end
position within the 256 bytes.

The 256 byte field also has a DS declared which provides an object
reference to this layout. This is used extensively in RPG programs to
enable the reference information to be mapped to individual fields.

I am writing some software to e-mail the reference information for
specific keys. 

I am using SQL to create the extract file as follows for our postcode
reference data (=US Zip Code):

create table qtemp/ed971 as (select * from $postcode) with no data

This works fine. This new table is now defined as having the same
structure as the $POSTCODE DS.

My problem now is, how do I get the 256 byte field into the individual
table fields? In RPG a simple move to the DS would map the values, but
I'm at a loss to work out how to achieve the same thing in SQL. Using
SELECT with substrings is no good because that requires knowledge of the
individual field posisions.

The INSERT INTO qtemp/ed971 statement seems to require data to be
referenced at the field level. 

Any advice gratefully acknowledged.