Additional SQL
Additional SQL statements can be used to construct customized SQL queries.
If the fixed filtering feature does not return the required result, you can extend this feature by constructing your own customized SQL queries in the List block, to display the required result.
For more information on additional SQL, refer to Appendix – Samples of Additional SQL.
 | Using this feature requires in-depth knowledge of SQL statements. |
 | For more information about the data structure of the database, see the Planon data dictionary (credentials required), an HTML page that contains detailed information of the Planon database and which is updated each time the data structure is changed. |
The additional SQL statements are added to the WHERE clause of the query.
Example
Select
<column name>
From
<table name>
Where
<condition>
<advanced SQL statements>
Order by
<column name>
<column name>
From
<table name>
Where
<condition>
<advanced SQL statements>
Order by
<column name>
Additional SQL statements must always start with AND or OR. If you want to filter on a field of the main table, you must prefix it with "MAIN_TABLE.". For example, "AND MAIN_TABLE.NAAM LIKE '%aa%'"
You can also use the following macros:
Macro | Description |
|---|---|
&DATASECTION | Retrieves the current data section. |
&PERSON | Retrieves the logged in person (the account needs to be linked to a person). |
&PERSON.PROPERTY | Retrieves the property of the logged in user. |
&PERSON.SPACE | Retrieves the space of the logged in user. |
&PERSON.DEPARTMENT | Retrieves the department of the logged in user. |
&PERSON.ADDRESS | Retrieves the address of the logged in user. |
&PERSON.COSTCENTER | Retrieves the cost center of the logged in user. |
For example, AND MAIN_TABLE.SYSMELDER IN (SELECT SYSCODE FROM PERS WHERE SYSCODE = [&PERSON])
| | You must write the macros within brackets. For example: [&PERSON]. |
Context fields
In addition to using macros it also possible to use context fields. Context fields can be used when the field that you want to filter on is not available as a search field.
The syntax of a context field tag is as follows: [&system_name_of_context_field]
| | • The system name is case sensitive. • Currently, only reference fields are supported. |
Example
For a Property field, use the following syntax: [&PropertyRef]
For filtering requestors based on a selected property, you can use the following SQL:
AND ((MAIN_TABLE.SYSCODEOBJ = [&PropertyRef]) OR ([&PropertyRef] = -1))
When the user did not enter a property, the OR statement will ensure that all requestors are displayed. If you leave out the OR statement, no requestors are displayed.
| | Additional SQL for Reservation Wizard Planboard - the Additional SQL for Reservation Wizard Planboard differs slightly from the way queries are usually constructed. The planboard queries are on the table RESEENH (reservation units). However, since there is an underlying complicated query, you cannot use MAIN_TABLE, but must use TABLE_1 which is derived from the ProCenter query. Example: and TABLE_1.NAAM LIKE '%roomname%'. This query would give all reservation units with a name like “roomname”. |