Conditionals
Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
Conditionals
Can I run insert / update commands based on the value of the 1st three chars in a text file ?
- Support_Duane Offline
- Support Specialist
- Posts: 66
- Joined: Thu Mar 05, 2009 3:49 pm
Re: Conditionals
Yes. Here is an example of a fixed width file with three record types - HDR, DTL and TRL - each of which needs to go into a different database file. In this example, those files will be iSeries files, but this will work with any database. Both the Fixed Width file and the Project file are attached.
Fixed Width File – dbod.txt
Heading database file DDS
Detail database file DDS
Trailer database file DDS
The project contains two tasks. The first will read the Fixed Width file, and create a rowset. The second task will create a temporary database file (in QTEMP on the iSeries, any other system will require another step to remove the file) from which the records can then be selectively processed.
Fixed Width File – dbod.txt
Code: Select all
HDGHeading line Stuff after heading x
DTLDetail line one More detail data more detail data for line one x
DTLDetail line two More detail data more detail data for line two x
DTLDetail line three More detail data more detail data for line three x
TRLTrailer line x
Code: Select all
A R DBODHR TEXT('Header Records')
A DATA1 20A COLHDG('Data' 'One')
A DATA2 20A COLHDG('Data' 'Two')
Code: Select all
A R DBODDR TEXT('Detail Records')
A DATA1 20A COLHDG('Data' 'One')
A DATA2 20A COLHDG('Data' 'Two')
A DATA3 37A COLHDG('Data' 'Three')
Code: Select all
A R DBODTR TEXT('Trailer Record')
A DATA1 20A COLHDG('Data' 'One')
Code: Select all
<project name="process FW file" mainModule="Main" version="1.0">
<module name="Main" logLevel="debug">
<!-- Read the fixed width file and create a four column rowset -->
<readFixedWidth label="read dbod.txt" inputFile="/gademo/dbod.txt" outputRowSetVariable="data" skipFirstRow="false" recordDelimiter="CRLF">
<data>
<column index="1" name="RecordType" type="CHAR" size="3" />
<column index="2" name="data1" type="CHAR" size="20" />
<column index="3" name="data2" type="CHAR" size="20" />
<column index="4" name="data3" type="CHAR" size="37" />
</data>
</readFixedWidth>
<sql label="Write to DBOD4" resourceId="Dev61">
<!-- Create a temporary file -->
<query label="create dbod4">
<statement>create table qtemp.dbod4
(rectyp CHAR(3),
data1 CHAR(20),
data2 CHAR(20),
data3 CHAR(37))</statement>
</query>
<!-- Fill the temporary file with data from the Fixed Width file -->
<query label="fill dbod4" inputRowSetVariable="${data}">
<statement>insert into qtemp.dbod4 values(?, ?, ?, ?)</statement>
</query>
<!-- Select and process the Header records -->
<query label="get hdg records" outputVariable="hdgData">
<statement>SELECT DATA1, DATA2 FROM qtemp.DBOD4 WHERE RECTYP = 'HDG' </statement>
</query>
<query label="clear dbodh">
<statement>delete from gatest.dbodh</statement>
</query>
<query label="write hdg records" inputRowSetVariable="${hdgData}">
<statement>insert into gatest.DBODH values(?,?)</statement>
</query>
<!-- Select and process the Detail records -->
<query label="get dtl records" outputVariable="dtlData">
<statement>SELECT DATA1, DATA2, DATA3 FROM qtemp.DBOD4 WHERE RECTYP = 'DTL' </statement>
</query>
<query label="clear dbodd">
<statement>delete from gatest.dbodd</statement>
</query>
<query label="write dtl records" inputRowSetVariable="${dtlData}">
<statement>insert into GATEST.DBODD values(?,?,?)</statement>
</query>
<!-- Select and process the Trailer records -->
<query label="get trl records" outputVariable="trlData">
<statement>SELECT DATA1 FROM qtemp.DBOD4 WHERE RECTYP = 'TRL' </statement>
</query>
<query label="clear dbodt">
<statement>delete from gatest.dbodt</statement>
</query>
<query label="write trl records" inputRowSetVariable="${trlData}">
<statement>insert into GATEST.DBODT values(?)</statement>
</query>
</sql>
</module>
</project>
- Attachments
-
- process FW file.xml
- Project file
- (2.15 KiB) Downloaded 949 times
-
- dbod.txt
- fixed width file
- (405 Bytes) Downloaded 944 times