There are certain fields in different tables which are identified by a common field-id which do have a standardized meaning and usage.
| Field | Size | Type | Description |
|---|---|---|---|
| INR | 8 | t | Unique internal ID of a record in a given table. This INR is a text field, which is usually created by retrieving the next valid entry from a counter. The counter-name is usually the name of the table. |
| VER | 4 | t | Version number of a master file record. Records which are logged in the modification log have to hold this field to store the sequence number of all modifications of a specific record. The specific record, which refers to the version number in the modification log, is identified by the INR. |
| xxxTYP OBJTYP | 6 | t | Object type identification. Usually the name of the table used to store the relevant entries. Often used together with xxxINR to identify exactly one record in a certain table. |
| xxxINR OBJINR | 8 | t | INR of a linked record in the table specified by the prefix or specified by a separate type identifying field. If no record is linked, the field holds a space value. |
| xxxKEY OBJKEY | 1-32 | t | External key of a linked record. This field is usually an externally known unique key to identify a record of a different table. Common usage might be the Host-call number of an address or the SWIFT-OSN of an incoming SWIFT message. This field is optional. This field should not be used for technical internal references to the record. It is intended for user-driven interactive selection and for logical linking, even if no technical INR link exists. |
| NAM xxxNAM | 40 | t | These name fields are used to identify records with longnames for a selection list or another display purpose. Searching within a table could usually be done via entering parts of this field. All matching records are usually displayed to select the requested one. |
| CUR xxxCUR | 3 | t | Currency to be used for the amount specified in xxxAMT |
| AMT xxxAMT | 18,3 | n | Amount field. Amount fields are always stored with 3 digits of decimals. Depending on the currency used (normally specified in xxxCUR or determined depending on the context) only less digits might be shown. Thus, there is no implicit floating decimal point depending on the currency and all amount fields might directly be used without aligning the field according to the decimal places specified per currency. |
| xxxACC | 16 | t | Account number in external notation. The external notation usually contains additional spaces and delimiting characters. |
| xxxMAR xxxRAT | 12,6 | n | Margin or Rate to be used for exchange rate calculations. The values used to convert currencies are always stored with 12 digits with 6 decimals. Thus, normally the exact entered values are stored. |
| BEGDAT | 12 | d | Begin date of a date range to describe the validity of an entry in the database. If no date is set, the entry is valid from the very beginning. A begin date is always including the specified date (see RNGMOD for more details regarding date ranges). |
| ENDDAT | 12 | d | End date of a date range to describe the validity of an entry in the database. If no date is set, the entry is valid until the very end. An end date is always excluding the specified date (see RNGMOD for more details regarding date ranges). |
| xxxTIM | 6,4 | n | Time of certain events. The time is stored as numeric item in hours. The minutes and seconds are stored as decimal fraction. The hundredth digits define the minutes and the tenthousandth digits define the seconds. |
| CREDAT | 12 | d | Date of creation of a contract. If this field is set, the contract has been entered and stored as a contract. This does not imply that the contract is a formal accepted contract. It has been entered and got a reference number and might be used for preadvices and other not legally binding business transactions. If this field is not set, the record has only be stored to reserve the reference number and store some advance information. |
| OPNDAT | 12 | d | Date of opening / issuing of a contract. If this field is set, the contract has been legally established and the contract might be used for business transactions. |
| CLSDAT | 12 | d | Date of closing a contract. If this field is set, the contract is closed and might no longer be used for transaction processing of business transactions. |
To select a certain entry, its INR should be used to access the entry. Depending on the location of the usage, the precise implementation differs. Usually, the INR values to be used should not be implemented into the SQL text like in the first of the following examples. They should be separated from the SQL command by the use of the Bind technology.
Embedded in SQL:
“WHERE INR = '” + xxxINR + “'”
Using the Bind technology:
“WHERE INR = ?”, xxxINR
The Bind technology is not possible in all SQL queries. However, it should be used, if applicable, because this results in an increased database performance.
To select all entries associated to a certain entry in a 1:N relation, the table ID of the parent entry is used as OBJTYP and its INR is used as OBJINR. Thus, all subentries hold the identification of the parent entry including the definition of the table, which holds the parent entry.
Embedded in SQL:
“WHERE OBJTYP = '” + OBJTYP + “' AND OBJINR = '” + OBJINR + “'”
Using the Bind technology:
“WHERE OBJTYP = ? AND OBJINR = ?”, OBJTYP, OBJINR
In order to select a record with a unique code according to the field COD, which is valid for a particular date (in the example it is the content of the field RELDAT), the following SQL code can be used:
Embedded in SQL:
“WHERE COD = '” + COD + “' AND ( BEGDAT <= ” + SqlDate( RELDAT ) + “ ) AND ( ENDDAT > ” + SqlDate( RELDAT ) + “ )”
Using the Bind technology:
“WHERE COD = ? AND ( BEGDAT <= ? ) AND ( ENDDAT > ? )”, COD, RELDAT, RELDAT
The module RNGMOD is supplied to make access and maintenance methods available.
Caution: Some ODBC-Driver of MS SQL-Server accepts “xxxDAT = NULL” without any warning or error message, but does not process it correctly. Only the term “xxxDAT IS NULL” is processed correctly. Therefore, to avoid possible error's, the syntax “xxxDAT IS NULL” should be used.