SQL-Expressions in instantOLAP
The SQL-Keyloaders and the SQL-Cubes use SQL-Expression for their definition. SQL-Expressions have a SQL-like syntax but only are fragments of a whole SQL-statement. The statements are assembled out of this fragments by the instantOLAP SQL-generator every time a loader or cube queries the database.
SQL-Expression also use types but the type-system is less restrictive than the one for expressions. Mainly the system only distinguishes between Strings, Numbers and Boolean-Expressions. Boolean-Expression are expected for all WHERE properties inside the loaders and cubes, all other properties allow any type.
Every time a loader or cube executes a query, the SQL-statement will be generated before. Basically each time a statement is generated the following steps will be performed:
- All needed SQL-expressions are collected
- All needed filters (WHEREs) are collected
- All used tables are collected from the expressions and filters to generate the FROM clause
- All links between the used tables are collected to generate the joins in the WHERE clause
- All non-aggregating expressions are added to the GROUP BY clause
If more than one table is used, the generator will automatically gather all available links between this tables and add them to the statements. If one or more tables can't be linked to the others (because there is no defined or imported link between them and the others), the generator will stop and raise an error. Selecting values from multiple tables without links is not supported!
Though instantOLAP generates statements in the ANSI SQL/92 syntax, some SQL features are still database-dependent:
- The concat-operator: Most databases use the ANSI syntax (||) to concatenate strings. But some database (MS Access, MS SQL Server, MySql) use different operator, e.g. the plus-sign or custom-functions.
- The maximum number of values in a WHERE clause using IN: instantOLAP uses IN-clauses to filter expressions for a large number of values (e.g. to filter products by their IDs). This IN-clauses can be become very long and may exceed the maximum length of lists or overall statements. In this case, the SQL-generator will split the statement and execute multiple queries. These lengths are also database-dependent.
- The usage of aliases in WHERE, GROUP BY and ORDER BY clauses: Some database don't allow the usage of complex expressions or functions in the GROUP BY, WHERE or ORDER BY clause and expect aliases instead. Other database don't support aliases in the clauses but allow complex expressions.
- Custom functions and custom aggregations: Most databases have custom functions which are not part of the ANSI SQL/92 syntax but can be very useful.
To cover these features, the database allows to configure most of the database-dependent properties (concat-operator, maximum value count for IN, usage of aliases) in their definition. Also, for the most common database-types these settings are already pre configured and will be used automatically when a pre-configured database is detected.
Only the custom-functions and -aggregation have to be inserted manually into the SQL-expressions by using the SQL Pass-through.
