Extending order# field

[Donna Rizzo]

In 2012, our order# will roll back to 1, something we can't let
happen. There are two choices.... to make the current 6 digit field
alpha or enlarge the field by 1 digit. There are over 1400 pgms
known to use the orders file as well as other files that contain the
order# but most will just need to be recompiled (either way) and
Hawkeye can take care of that.
This isn't a unique problem so who out there has been faced with
converting a primary field and based on your experience, which choice
would you suggest?
Thank You

[WDS]

How long has the current scheme lasted? I would say stick with numbers
and extend the size but consider using 2 (or more!) extra digits instead
of just 1.

I was bitten when a formerly numeric-only field became alphanumeric when
they ran out of numbers. The code would have been easier to change for
longer numbers rather than mixed alphanumerics.

Later they then extended it by an extra character. Argh!

[Donna Rizzo]

That's funny ! The 6 digits have worked for over 20 years but about
4 years ago we took on a client that brings mega number of orders and
they like to keep 5 years of order history on the system. After that
we can flip back to 1.
We also have a one digit backorder field that we concatenate on
screens and reports with a hyphen ( xxxxxx-x )so expanding it one
digit will allow us to make changes without having to worry about
finding room for that one extra digit (in some cases) if we simply
remove the hyphen.
We're leaning towards expanding the size and keeping it numeric....
Thanks so much for the quick response !

[CRPence]

On 14-Dec-2011 11:38 , Donna Rizzo wrote:
> In 2012, our order# will roll back to 1, something we can't let
> happen. There are two choices.... to make the current 6 digit
> field alpha or enlarge the field by 1 digit.

I am not sure I understand why a resolution would be restrictive to
only those two choices. Regardless, I believe the current definition of
the ORDER# column [FWiW: using the '#' hash symbol is a poor choice for
naming] could be relevant for making a decision about how best to
resolve the concern. For example, a change from DECIMAL(6) to
DECIMAL(7) is much easier for the database than another change, since
the amount of storage [four bytes] and even the data remains unchanged.
A change to integer from another numeric type allows nine or ten
digits, and depending on the original type, the integer may take the
same or two fewer bytes of storage.

Obviously if there is code which increments the order-number value,
using the inherent arithmetic algorithms for numeric data is easier than
replacing any arithmetic with custom algorithms.

> There are over 1400 pgms known to use the orders file as well as
> other files that contain the order# but most will just need to be
> recompiled (either way) and Hawkeye can take care of that.

Good to have tools to help with that, but there are design options
which could help to limit the number of affected programs if\when that
might be desirable. A change to character would require finding every
use of the "number", e.g. arithmetically, throughout that code; whereas
keeping the data numeric should allow any arithmetic algorithms to
remain unchanged. Seems sticking with numeric could be the more
appealing transition.

> This isn't a unique problem so who out there has been faced with
> converting a primary field and based on your experience, which
> choice would you suggest?

External factors often are found to be the arbiter. If forms exist
that include the order-number, and that information is already
restricted to a specific number of characters, then how the database and
the programs want to change might be similarly restricted.

I think the best bet is with either picking the largest available
column definition for expansion, or a design change which either removes
[presumed to be unlikely given the current stated conclusion] or
ameliorates the concern against reusing the numbers. Since even a
larger-capability representation could experience the same difficulty as
the currently smaller-capability representation, depending on the
potential for growth, a design change would be the better option if the
possibility exists to encounter the limits again. Obviously any design
change is improbable given the implied constraints; regardless...

Ameliorating concerns for reuse might be achieved by correlating an
order number with another value which will effectively expand the
capability of the field, without any change to the field itself. The
order number could be tied-to a specific customer rather than maintained
unique across all customers. Or a new column could define an additional
digit; i.e. effectively the number of times the order number has
overflowed as a base-ten number. Or if the number of orders could be
known to never rollover within a year, the year could become an
attribute of the order number; the full date of the order making the
value even more unique. Like an overflow digit, the extra data could be
seemingly irrespective of time, simply adding another digit in a
separate column, where all "old" orders might use a default of the
digit=one to identify the "first generation" of orders, whereas the
current orders would utilize a default of the digit=two to identify the
"second generation" of orders. A change to the "default value"
specification of the column in the database to match the current
assumption can even handle some situations without a recompile.

Regards, Chuck

[Ken Sims]

Hi Donna -

Quite a number of years ago we had a three-digit supplier number field
that needed to be expanded. In our situation we had no choice. The
field had to stay numeric for compatibility with other software on the
system. We went from three digits to six digits (the maximum number
of digits that the other software supported).

The two biggest problems were:

1. Finding all of the fields that needed to be changed. (The field is
in a lot of files and the field names are not consistent. We also use
Hawkeye, and I wrote queries directly against the Hawkeye
cross-reference files to help find everything.)

2. Dealing with reports that didn't have enough room. (The screens
generally had room for expansion.)

We have a small I.T. department and our programming projects are
mostly just one-man projects, but all three of us worked together on
this. Because I'm a natural-born techno-nerd, I coordinated the
project.

As I recall, we split the project into three or four phases, but I
don't remember what the phases were.

--
Ken
Opinions expressed are my own and do not necessarily represent the views
of my employer or anyone in their right mind.

[Jonathan Ball]

Besides Hawkeye, I wonder if you might get a bargain on some leftover
Y2K impact analysis and remediation packages, if you can find them? I
wish I could remember the name of the package I used to do the Y2K fix
at an insurance company. It was a good product. It caught the vast
majority of year fields that we expanded from PIX X(002) to PIC X(004).

I'd probably go with the order number expansion, too.

[eb]

Our typical approach is add a new 7 digit order number field.
Recompile everything.
Start populating the 7 digit field
Modify all programs that use the 6 digit field, to use the 7 digit
field
When the 6 digit field is no longer needed, abandon it,
Or delete it and recompile everything.

When we resized our Order Number in our Open Order Database, it took
us months to change all the reports and other database related
workfile to use the longer Order Number.

Increasing the original field and recompiling would not work for us.
There would have been report spacing problems, key length differences
when chaining to work files, etc, etc, etc.....