Conditionals
Posted: Mon May 04, 2009 11:57 am
Can I run insert / update commands based on the value of the 1st three chars in a text file ?
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
A R DBODHR TEXT('Header Records')
A DATA1 20A COLHDG('Data' 'One')
A DATA2 20A COLHDG('Data' 'Two')
A R DBODDR TEXT('Detail Records')
A DATA1 20A COLHDG('Data' 'One')
A DATA2 20A COLHDG('Data' 'Two')
A DATA3 37A COLHDG('Data' 'Three')
A R DBODTR TEXT('Trailer Record')
A DATA1 20A COLHDG('Data' 'One')
<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>