Rounding off using schema files

[TonyInFrance]

Morning / Afternoon / Evening folks...

So I have this schema file as follows:

record {delim=';',final_delim= 'end_of_record' } (
COETF:nullable string[max=3] { null_field=' '};
...
...
MKRDU:nullable decimal[11,2] {null_field=''};
)

The value in the flat file I am coming across is a decimal but which has 3 digits after the decimal point.

Thus my job is truncating this value and considering just 2 places.

Is there anyway to ensure that the entire value is read and rounded off to 2 decimal places?

Thanks & regards

Tony

[ray.wurlod]

Not in the schema file. That is a transformation; you would specify it in a Modify or Transformer stage.

[TonyInFrance]

In that case i can't use a generic job to process and integrate multiple flat files into respective datasets. I've got RCP activated and my job consists of a sequential input stahe, a column import stage (which uses the schema file) and a dataset stage.
This job is called around a 100 times and each time the sequential file name, schema file name and dataset name is passed as a parameter.

If I have to use a modify stage after the column import I would have to declare the name of the column to modify right? This column is probably present in one of the 100 files I'm processing. Won't this be a problem?

[ray.wurlod]

Actually you can take advantages of one of the more obscurely documented features of the Modify stage - entire specifications can be job parameters. So, even if the column name needing rounding is dynamic, you can manage this by creating the specification as a job parameter.

[TonyInFrance]

Thanks Ray. I will try this.
But I hope in case this column is not present at all it won't be an irritant.
This is because among my 100 odd files some have decimals (lets call them Dec001, Dec002, Dec003, etc) and others don't have 'em at all or don't need rounding off.

[eph]

Hi,

You could create a configuration file at the same time you create the schema file. This way, you can parse your configuration before calling the job, sending the right modification spec.

Eric

[TonyInFrance]

Please elaborate a bit Eric. I'm not sure I understand your solution but it seems super interesting.

[eph]

It purely depends on how you build your schema files. Manually? Automatically? Using a Unix script? An excel macro? Then you can build something like

Code: Select all

<schema file name 1>|<modify spec>
<schema file name 2>|<modify spec>

Then with a command like below retrieve the modify spec and use it calling the job :

Code: Select all

grep <schema file name 1> <your conf file> | cut -d'|' -f2

You can also build your own script to generate the modify spec by parsing your schema file

Something like (not tested) :

Code: Select all

grep decimal <your schema file name> > tempfile
cat tempfile | while read line;do;
field=$(echo $line | cut -d':' -f1);
entPart=$(echo $line | cut -d'[' -f2 | cut -d',' -f1);
decPart=$(echo $line | cut -d',' -f2 | cut -d']' -f1);
if [ $decPart -le 2 ];
then continue ;
else
<create your spec here using $field and $entPart and your requirements >;
fi;

Of course you can do something much cleaner

[edit]
I forgot to mention that you might need to modify you schema file first, otherwise your field will be truncated before import. Maybe importing using unbounded varchar and then converting it using the right rounding function (a nice summary of the modify)?
[/edit]