Managed to avoid control files for most of my time as a DBA, but somehow managed to get landed with a small change!
Very simle question I'm sure:
We have data which comes in (for example): 1234567887654321
Control file at the moment is:
field1 POSITION(1:8) INTEGER EXTERNAL
field2 POSITION(9:17) INTERGER EXTERNAL
so field1=12345678 and field2=87654321
In the batch code, these are divided by 100 when updating the main tables. What I thought would be better, as these may be different for each of 12 countries, is to place a calculation in the relevant control file (so instead of 12 different 'IF's' in the code, we just have 12 different control files).
The database tables which are loaded into are:
field1 & field2 NUMBER(12,2).
So do I put:
field1 POSITION(1:8) INTEGER EXTERNAL ":field1/100"
field2 POSITION(9:17) INTEGER EXTERNAL ":field2/100"
or (length of the column performing calculation on)
field1 POSITION(1:8) INTEGER EXTERNAL(8) ":field1/100"
field2 POSITION(9:17) INTEGER EXTERNAL(8) ":field2/100"
or (length of calculated column)
field1 POSITION(1:8) INTEGER EXTERNAL(6,2) ":field1/100"
field2 POSITION(9:17) INTEGER EXTERNAL(6,2) ":field2/100"
or (length of database column)
field1 POSITION(1:8) INTEGER EXTERNAL(12,2) ":field1/100"
field2 POSITION(9:17) INTEGER EXTERNAL(12,2) ":field2/100"
Which would be correct - or doesn't it matter?
Thanks,
Terry
