< * | *** >
Basics 
- flat select
- group by select
- callback basics
- common aggregation operators
- other aggregation operators
- orphans management and dimension priority
- escape quote
- Stormbase & Lua
- utility Lua functions
- callback Lua functions
- data model modification #1
- data model modification #2
- where clause basics
- where clause / Lua boolean function
- (not)exists basics
- col_type_checks basics
- col_type_checks advanced
- permissions
- dynamic sql (advanced)
Basics
flat select
select <*|table1.*|col1|table1#col1>, ... from ...group by select
select col1, col2, ... from ... group by col1, col2, ... group by clause can be ommitedcallback basics
Ths callback allows to run functions on the resultset
select .. from .. <callback|cb> callback_function1 callback_function2 ...cb is a shortcut for callback
most common callbacks are sort and limit
more info are provided in "callback Lua functions" section
common aggregation operators
<sum|count|countdistinct|min|max|avg>other aggregation operators
<unique|countsequence|minstr|maxstr|list|ival|ivallist|ivalbin|p>Note: uniquesoft can be used instead of unique for backward compatibility, behavior is the same
orphans management and dimension priority
Column item_id in view v_sales can come from table sales or table items
For artA and artB we don't care
For artC we do care because artC is a orhan record (*)
By default SB will look for item_id in non empty (**) dimension tables (***), then in the fact table
But you can tell SB which one you want using table#col syntax
(*) exists in fact table but not in dimension table
(**) an empty dimension is skipped
(***) dimension1 is the second table of from clause of the view create statement etc...,
--> !!! the order of dimensions in the from clause of the view create statement is important
escape quote
''Stormbase & Lua
Lua is a progamming langage. Stormbase is not developped in Lua, it is developped in native C. But in some cases (callbacks and where clause, see here under) Stormbase calls Lua code. Standard Lua code (provided in Stormbase installer) is located in _FUNCTIONS/stormbase.lua. Custom Lua code can also be added in _FUNCTIONS/custom.lua.utility Lua functions
callback Lua functions
data model modification #1
...data model modification #2
Since v1.17.08 SB is a lot more flexible
where clause basics
select .. from ... <where|and> where_clause1 and where_clause2 and where_clause3 ..Where clause syntax
column_name='value' column_name in ('value1', 'value2', ...) column_name nin ('value1', 'value2', ...) column_name like 'value'where clause / Lua boolean function
any Lua boolean function can be used in a where clause using one and only one column(not)exists basics
select .. from view1and view2.col='something' Exists
and view2!.col='something' Not exists
The exists join between view1 and view2 is implicit (the first col_type in common will be used for join)
Use case: when view2 is based on a non merge table, which why it can't be included in view1
col_type_checks basics
A col_type_check is a "select col, count(col) from .. where ... group by col"
It is used to filter other part of the sql
The filter will be applied on the first column that has same col_type as the col_type_check
If you alias your group by column in the with clause, the filter with apply in priority to a column with this name (if it exists)
with with_name1 ( col_type_check1 ), with_name2 ( col_type_check2 ), ... select .. from view1, with_name1, with_name2! ..col_type_checks advanced
Col_type_checks can be combined to do intersection etc...
It is used to filter other part of the sql
with with_name1 ( col_type_check1 ), with_name2 ( select col, count(col) from view2, with_name1 where ... group by col ), ... select .. from view3, with_name2permissions
end_user col_type