Importing Zoned Decimal

T_Bekker: Posts: 8; Joined: Fri Aug 10, 2012 11:55 am

by T_Bekker » Sat Nov 16, 2013 2:22 pm

I have a fixed-width file that includes a numeric amount field, that can sometimes contain a negative value. The value -16.34 appears in the file as 00000163M. I need to insert this data into an iSeries file, where the field is defined as Zoned Decimal.

I've tried various type formats on the Read Fixed-Width > Column > Type Conversion tab. Every time the project executes, it fails on this record, with invalid data type.

How do I do this?

Re: Importing Zoned Decimal

Support_Rick Support Specialist

Posts: 592

Joined: Tue Jul 17, 2012 2:12 pm

Location: Phoenix, AZ

by Support_Rick » Sun Nov 17, 2013 1:01 pm

This issue has most to do with the way data is originally retrieved from the IBMi from the Zone Decimal value. If a sign is included, then based on hex values, it replaces the last digit with it's Hex Representation, instead of the actual number.

I would first look at making sure you retrieve the value from your IBMi with an SQL Select Statement to get the actual value with the sign. If this isn't possible, then I've attached a "Utility" I created that will translate the Zoned Decimal Number to the actual number if needed.

This could be called from within your project by doing a callProject and passing it the value that needs to be converted. The return value would be used to insert into your table.

If you have any other questions, please let me know...

Example of how to call the Utility

Code: Select all

<callProject label="Convert ZD Value" project="/Utilities/Convert Zoned Decimal Data" runInSameJob="true" inheritUserVariables="true" returnUserVariables="true" mode="interactive" version="1.0">
	<variable name="ZDFrom" value="${ MyZonedValue }" />
	<variable name="ZDTo" value="0" />
</callProject>

<setVariable label="My Translated Value" name="MyTranslatedValue" value="${ ZDTo }" version="2.0" />

Convert Zoned Decimal Value (Must be a GAD Version 4.0.0+)

Code: Select all

<project name="Convert Zoned Decimal Data" mainModule="Main" version="2.0" logLevel="silent">

	<description>Converts a value from the Zoned Decimal Representation to actual value</description>

	<module name="Main">

		<if label="Check Parm for Value" condition="${ IsEmpty( ZDFrom ) }">
			<raiseError label="No Value Passed" version="1.0">
				<message>The Parameter ZDFrom has no value to convert.  Please retry with value.</message>
			</raiseError>
		</if>

		<setVariable label="Get Length of ZDFrom" name="FrLen"    value="${ Length( Trim( ZDFrom ) ) }"           version="2.0" />
    <setVariable label="Get Convert Value"    name="CvtVar"   value="${ Substring( ZDFrom, FrLen, 1 ) }"      version="2.0" />

    <setVariable label="Positive Number?"     name="Pos"      value="${ PositionOf( PosNums, CvtVar ) -1 }"   version="2.0" />
    <setVariable label="Negative Number?"     name="Neg"      value="${ PositionOf( NegNums, CvtVar ) -1 }"   version="2.0" />

    <if label="No conversion?" condition="${ Pos + Neg == -4 }" >
      <setVariable label="New ZDTo"     name="ZDTo"      value="${ ZDFrom }"  version="2.0" />
      <exitModule/>
    </if>

    <if label="New POS Number" condition="${ Pos ge 0 }" >
      <setVariable label="New ZDTo"     name="ZDTo"      value="${ Replace( ZDFrom, CvtVar, Pos ) }"  version="2.0" />
    </if>
    <if label="New NEG Number" condition="${ Neg ge 0 }" >
      <setVariable label="New ZDTo"     name="ZDTo"      value="${ Replace( ZDFrom, CvtVar, Neg ) }"  version="2.0" />
    </if>

		<print label="(Status) Conversion" version="1.0">
			<![CDATA[
==============================================
Conversion was made...
ZDFrom:  ${ ZDFrom }
ZDTo:    ${ ZDTo }
==============================================
      ]]>
		</print>
 	</module>

	<variable name="ZDFrom"  value=""  description="Convert from Zone Decimal Value" />
	<variable name="ZDTo"    value=""  description="Convert To Zoned Full Value" />

	<variable name="PosNums" value="{ABCDEFGHI" description="Postive Numbers" />
	<variable name="NegNums" value="}JKLMNOPQR" description="Netative Numbers" />
	<variable name="Numbers" value="0123456789" description="The Numbers" />

</project>

Last edited by Support_Rick on Mon Nov 18, 2013 5:16 pm, edited 1 time in total.
Reason: Added If Statment in case no conversion was needed

Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

Re: Importing Zoned Decimal

T_Bekker: Posts: 8; Joined: Fri Aug 10, 2012 11:55 am

by T_Bekker » Sun Nov 17, 2013 5:48 pm

The data file is coming from an external source, and I am trying to load it into the IBMi file. This works fine when using other tools we have for the System i - the interpretation of the hex character is automatic.

I am willing to try this, but it seems I have to loop through each record, load the specific column into a variable, pass it to this routine to translate it, then insert the translated value into the database, along with the other columns. So the loop will perform a SQL insert for each record in the file. Right?

I'll give it a shot... Thanks