Peoplesoft allows you to create integer fields by specifying a number field with a given number of “Integer Positions” and Zero “Decimal Positions”. Application Designer maps these definitions at table create time to the underlying integer data types in the database. The data type the underlying table column gets depends on the number of integer positons specified. For example, on SQL Server any integer field up to 5 digits will map to a SMALLINT in the database. Integers with 6 to 10 digits will map to an INT. Beyond that a decimal field is used.
This introduces some interesting “features” of these fields:
- If you define a two digit unsigned integer in Application Designer, then Peoplesoft will limit the input values to 00 through 99. But the column definition in the database will allow positive and negative values in the range -32,768 to +32,767. Any attempt to insert values outside this range will elicit a Arithmetic Overflow error from the database as you would expect.
- As the database can accept values above the range that Peoplesoft allows, inserts into the tables using SQL in an Application Engine could potentially create values that would not display correctly within the application itself.
- A 5 digit integer in PeopleSoft can have values up to 99999 – but the database will error above +32,767. A nice little “gotcha” there … 🙂
- More interestingly if you decided to change a two digit unsigned integer field to (say) a 5 digit integer you would not need to alter the underlying table. In fact, Application Designer would not even generate a script for you as there would be nothing to change – 1 to 5 digit integers all map to a SMALLINT. If you increased the field to 6 or more integers then a change would be required as this would be mapped to an INT or even a DECIMAL in the database.