The information in this article should be used in conjunction with Knowledge Share article “How to: Interrogate the Backend SQL Table using IBM Controller”
The Period Table
The most widely used data table for SQL query is the Period Table. This is presented in the ID format “XDBxx”. Here the “xx” represents the year of the period being reported against. For example, data posted to period 2406AC would sit on period table XDB24.
The following table summarises the purpose or description belonging to each of the columns within this Period Table.
| Field Name | Description |
| BELOPP | Amount |
| BOL | Company |
| BTYP | Journal Type |
| DIM1 | Dimension 1 |
| DIM2 | Dimension 2 |
| DIM3 | Dimension 3 |
| DIM4 | Dimension 4 |
| ETYP | Automatic Journal Type |
| INO | Automatic Identification Number |
| KONTO | Account Code |
| KTYPKONC | The group type and group to which values from the elimination of the acquisition calculations are booked. |
| MOTBOL | Counter Company used for intercompany balances and shareholdings. |
| MOTDIM | Counter Dimension used for eliminations of intercompany balances. This is only valid for dimension 1. |
| PERAKT | Period and Actuality |
| TRAVKD | Transaction Currency Code . |
| TRBELOPP | Transaction Currency Amount |
| URSBOL | Original Company (is different from BOL after consolidation), which reported the intercompany balance. |
| VERNR | Journal Number |
| VKOD | Currency Code |
| VTYP | Currency Type, where the field indicates if a journal has been entered in another currency than the local currency. |
Table Operators or Test Selections #
To navigate within the likes of this Period Table within SQL, you will need to use operators, as either selected or typed into the Test Field.
As illustrated, there are several different selections that can be used, each of which is described as follows:
| = | Shows all values or strings equal to the defined criteria. For example, konto = ‘1390’ only displays the account 1390 in the result window. |
| <> | Shows all values or strings separated from the defined criteria. |
| < | Shows all values or strings less than the defined criteria. |
| <= | Shows all values or strings less than or equal to the defined criteria. |
| > | Shows all values or strings greater than the defined criteria. |
| >= | Shows all values or strings greater than or equal to the defined criteria. |
| IN | Shows all values or strings you specify. The test values are entered within parentheses and single citation marks. When entering several test values, they are separated by a comma (,). For example, konto IN (‘1310′,’2081’) will display both account 1310 and account 2081. |
| NOT IN | Shows all values or strings apart from the ones you specify. The test values are entered within parentheses and single citation marks. When entering several test values, they are separated by a comma (,) For example, konto NOT IN (‘1310′,’2081’) shows all accounts except 1310 and 2081. |
| BETWEEN | Shows all test values within the specified range. Separate the start value and the end value with AND. For example, konto BETWEEN ‘1310’ AND ‘2081’ shows all accounts between the accounts 1310 and 2081 |
| LIKE | Shows all test values within the specified criteria. Here you can use wild cards to search for similar strings or values. For example, konto LIKE ‘131_’ shows all four character accounts starting with 131. Another example, fkod LIKE ‘FR%’ shows all dimension codes starting with FR. |
| NOT LIKE | Shows all test values except the specified criteria. Here you can use wild cards to search for similar strings or values. For example, fkod NOT LIKE ‘FR%’ shows all dimension codes except the ones starting with FR |
The latest IBM Controller production documentation can be found on the IBM website – here
