-- ###########################
-- RUNNING stop_on_error.sql
stop_on_error;


reponse



success

reponse
success

-- ###########################
-- RUNNING clear.sh

  
  /*
  sure mode, no confirmation prompt
  clearing directory INTERNAL_FILES_DIR (../STORAGE/INT) ...
  done
  clearing directory MAX_PERF_FILES_DIR (../STORAGE/MAX_PERF) ...
  done
  clearing directory TRANSAC_FILES_DIR (../STORAGE/TRANSAC) ...
  done
  */

-- ###########################
-- RUNNING doc_data_init.sql
create col_type t_site_id as text;


reponse



success

reponse
success

create col_type t_dept_id as text;


reponse



success

reponse
success

create col_type t_item_id as text;


reponse



success

reponse
success

create col_type t_customer_id as text;


reponse



success

reponse
success

create col_type t_date as text;


reponse



success

reponse
success

create col_type t_customer_info as text;


reponse



success

reponse
success

create col_type end_user as text;


reponse



success

reponse
success

  create merge table items( item_id t_item_id, art_label text, dept t_dept_id, avg_week_sales number, sales_price number);


reponse



success

reponse
success

create merge table customers( customer_id t_customer_id, customer_name text);


reponse



success

reponse
success

create table item_tags( item_id t_item_id, tag text);


reponse



success

reponse
success

  create table fidelity_cards( customer_id t_customer_id, card_label t_customer_info, valid_from t_date, valid_until t_date);


reponse



success

reponse
success

  create table item_customer_infos( customer_id t_customer_id, item_id t_item_id, info t_customer_info, valid_from t_date, valid_until t_date);


reponse



success

reponse
success

  create big table sales( item_id t_item_id, customer_id t_customer_id, sales_date t_date, sales_qty number, line_id text, packaging_id t_item_id);


reponse



success

reponse
success

create big table inventory( item_id t_item_id, inv_qty number);


reponse



success

reponse
success

create view v_items as select * from items;


reponse



success

reponse
success

create view v_item_tags as select * from item_tags;


reponse



success

reponse
success

create view v_fidelity_cards as select * from fidelity_cards;


reponse



success

reponse
success

create view v_item_customer_infos as select * from item_customer_infos;


reponse



success

reponse
success

  create view v_sales as select * from sales, items, customers where items.item_id=sales.item_id and customers.customer_id=sales.customer_id;


reponse



success

reponse
success

create view v_inventory as select * from inventory, items where items.item_id=inventory.item_id;


reponse



success

reponse
success

insert into items values('artA','the article A','dept #1',10,1.5);


reponse



success

reponse
success

insert into items values('artB','the article B','dept #2',10,3.2);


reponse



success

reponse
success

insert into items values('box1','a box','packaging',10,0);


reponse



success

reponse
success

insert into customers values('C1','customer #1')('C2','customer #2');


reponse



success

reponse
success

insert into item_tags values('artA','tag #1');


reponse



success

reponse
success

insert into item_tags values('artA','tag #2');


reponse



success

reponse
success

insert into fidelity_cards values('C1','SILVER','20191201','20191231');


reponse



success

reponse
success

insert into fidelity_cards values('C1','GOLD','20201201','20201231');


reponse



success

reponse
success

insert into item_customer_infos values('C1','artA','FREQUENT BUYER of artA in 2019','20190101','20191231');


reponse



success

reponse
success

insert into item_customer_infos values('C1','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');


reponse



success

reponse
success

insert into item_customer_infos values('C2','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');


reponse



success

reponse
success

insert into sales values('artA','C1','20191231',5,'ID01','box1');


reponse



success

reponse
success

insert into sales values('artB','C2','20200102',6,'ID02','');


reponse



success

reponse
success

insert into sales values('artB','C1','20191231',4,'ID03','');


reponse



success

reponse
success

insert into sales values('artB','C2','20200102',7,'ID04','box1');


reponse



success

reponse
success

insert into sales values('artC','C1','20200102',8,'ID05','');


reponse



success

reponse
success

insert into sales values('artA','C1','20191231',5,'ID06','box1');


reponse



success

reponse
success

insert into sales values('artA','C2','20191231',5,'ID07','box1');


reponse



success

reponse
success

insert into inventory values('artA',32);


reponse



success

reponse
success

insert into inventory values('artC',12);


reponse



success

reponse
success

refresh dirty view;


reponse



success

reponse
success

-- ###########################
-- RUNNING doc_basics.sql
--

Basics

--

flat select

select <*|table1.*|col1|table1#col1>, ... from ...

desc view;


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count



v_items 1 3 n n n 10 0 13 13 0 0
v_item_tags 1 2 n n n 10 0 12 12 0 0
v_fidelity_cards 1 2 n n n 10 0 12 12 0 0
v_item_customer_infos 1 3 n n n 10 0 13 13 0 0
v_sales 3 7 n n n 10 1 16 17 0 0
v_inventory 2 2 n n n 10 0 12 12 0 0

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_rare_count	mp_often_count	mp_count
v_items	1	3	n	n	n	10	0	13	13
v_item_tags	1	2	n	n	n	10	0	12	12
v_fidelity_cards	1	2	n	n	n	10	0	12	12
v_item_customer_infos	1	3	n	n	n	10	0	13	13
v_sales	3	7	n	n	n	10	1	16	17
v_inventory	2	2	n	n	n	10	0	12	12

select * from v_items;


item_id art_label dept avg_week_sales sales_price



artA the article A dept #1 10 1.500
artB the article B dept #2 10 3.200
box1 a box packaging 10 0

item_id	art_label	dept	avg_week_sales	sales_price
artA	the article A	dept #1	10	1.500
artB	the article B	dept #2	10	3.200
box1	a box	packaging	10	0

select * from v_item_tags;


item_id tag



artA tag #1
artA tag #2

item_id	tag
artA	tag #1
artA	tag #2

select * from v_sales;


item_id customer_id sales_date sales_qty line_id packaging_id item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 artA the article A dept #1 10 1.500 C1 customer #1
artB C2 20200102 6 ID02 # artB the article B dept #2 10 3.200 C2 customer #2
artB C1 20191231 4 ID03 # artB the article B dept #2 10 3.200 C1 customer #1
artB C2 20200102 7 ID04 box1 artB the article B dept #2 10 3.200 C2 customer #2
artA C1 20191231 5 ID06 box1 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 artA the article A dept #1 10 1.500 C2 customer #2
# C1 20200102 8 ID05 # # # # 0 0 C1 customer #1

item_id	customer_id	sales_date	sales_qty	line_id	packaging_id	item_id	art_label	dept	avg_week_sales	sales_price	customer_id	customer_name
artA	C1	20191231	5	ID01	box1	artA	the article A	dept #1	10	1.500	C1	customer #1
artB	C2	20200102	6	ID02	#	artB	the article B	dept #2	10	3.200	C2	customer #2
artB	C1	20191231	4	ID03	#	artB	the article B	dept #2	10	3.200	C1	customer #1
artB	C2	20200102	7	ID04	box1	artB	the article B	dept #2	10	3.200	C2	customer #2
artA	C1	20191231	5	ID06	box1	artA	the article A	dept #1	10	1.500	C1	customer #1
artA	C2	20191231	5	ID07	box1	artA	the article A	dept #1	10	1.500	C2	customer #2
#	C1	20200102	8	ID05	#	#	#	#	0	0	C1	customer #1

select sales.* from v_sales;


item_id customer_id sales_date sales_qty line_id packaging_id



artA C1 20191231 5 ID01 box1
artB C2 20200102 6 ID02 #
artB C1 20191231 4 ID03 #
artB C2 20200102 7 ID04 box1
artA C1 20191231 5 ID06 box1
artA C2 20191231 5 ID07 box1
# C1 20200102 8 ID05 #

item_id	customer_id	sales_date	sales_qty	line_id	packaging_id
artA	C1	20191231	5	ID01	box1
artB	C2	20200102	6	ID02	#
artB	C1	20191231	4	ID03	#
artB	C2	20200102	7	ID04	box1
artA	C1	20191231	5	ID06	box1
artA	C2	20191231	5	ID07	box1
#	C1	20200102	8	ID05	#

--

group by select

select col1, col2, ... from ... group by col1, col2, ...
group by clause can be ommited

select item_id, count(*) from v_items group by item_id;


item_id count(*)



artA 1
artB 1
box1 1

item_id	count(*)
artA	1
artB	1
box1	1

select item_id, count(*) from v_item_tags group by item_id;


item_id count(*)



artA 2

item_id	count(*)
artA	2

select customer_id, dept, avg_week_sales, count(*) from v_sales group by customer_id, dept, avg_week_sales;


customer_id dept avg_week_sales count(*)



C1 dept #1 10 2
C2 dept #2 10 2
C1 dept #2 10 1
C2 dept #1 10 1
C1 # # 1

customer_id	dept	avg_week_sales	count(*)
C1	dept #1	10	2
C2	dept #2	10	2
C1	dept #2	10	1
C2	dept #1	10	1
C1	#	#	1

--group by can be ommited if group by is on text columns
select customer_id, dept, count(*) from v_sales;


customer_id dept count(*)



C1 dept #1 2
C2 dept #2 2
C1 dept #2 1
C2 dept #1 1
C1 # 1

--

callback 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

select customer_id, count(*) from v_sales group by customer_id
callback sort(2,'desc');


customer_id count(*)



C1 4
C2 3

select customer_id, count(*) from v_sales group by customer_id
callback sort(2,'asc') limit(1);


customer_id count(*)



C2 3

select customer_id, count(*) from v_sales group by customer_id
cb sort(2,'asc') limit(1);


customer_id count(*)



C2 3

select customer_id, count(*)as nb_sales from v_sales group by customer_id
cb sort('nb_sales','asc') limit(1);


customer_id nb_sales



C2 3

--

common aggregation operators

<sum|count|countdistinct|min|max|avg>

--sum (number columns only)
select customer_id, sum(sales_qty), sum(avg_week_sales) from v_sales group by customer_id;


customer_id sum(sales_qty) sum(avg_week_sales)



C1 22 30
C2 18 30

--count
select customer_id, count(item_id), count(sales_qty), count(avg_week_sales) from v_sales group by customer_id;


customer_id count(item_id) count(sales_qty) count(avg_week_sales)



C1 3 4 3
C2 3 3 3

--countdistinct

  select customer_id, countdistinct(item_id), countdistinct(sales_qty), countdistinct(avg_week_sales) from v_sales group by customer_id;


customer_id countdistinct(item_id) countdistinct(sales_qty) countdistinct(avg_week_sales)



C1 2 3 1
C2 2 3 1

--min
select customer_id, min(item_id), min(sales_qty), min(avg_week_sales) from v_sales group by customer_id;


customer_id min(item_id) min(sales_qty) min(avg_week_sales)



C1 artA 4 10
C2 artA 5 10

--max
select customer_id, max(item_id), max(sales_qty), max(avg_week_sales) from v_sales group by customer_id;


customer_id max(item_id) max(sales_qty) max(avg_week_sales)



C1 artB 8 10
C2 artB 7 10

--avg (number columns only)
select customer_id, avg(sales_qty), avg(avg_week_sales) from v_sales group by customer_id;


customer_id avg(sales_qty) avg(avg_week_sales)



C1 5.500 10
C2 6 10

--

other aggregation operators

Note: uniquesoft can be used instead of unique for backward compatibility, behavior is the same

select customer_id, unique(line_id), unique(item_id), unique(customer_id) from v_sales group by customer_id;


customer_id unique(line_id) unique(item_id) unique(customer_id)



C1 ID05 # C1
C2 # # C2

select customer_id, countsequence(item_id) from v_sales group by customer_id;


customer_id countsequence(item_id)



C1 3
C2 2

select customer_id, minstr(item_id) from v_sales group by customer_id;


customer_id minstr(item_id)



C1 artA
C2 artA

select customer_id, maxstr(item_id) from v_sales group by customer_id;


customer_id maxstr(item_id)



C1 artB
C2 artB

select customer_id, list(item_id) from v_sales group by customer_id;


customer_id list(item_id)



C1 artA,artB
C2 artA,artB

select customer_id, ival(item_id), ival(customer_id) from v_sales group by customer_id;


customer_id ival(item_id) ival(customer_id)



C1 # 11
C2 # 12

select customer_id, ivallist(item_id), ivallist(customer_id) from v_sales group by customer_id;


customer_id ivallist(item_id) ivallist(customer_id)



C1 11/12 11
C2 11/12 12

select customer_id, ivalbin(item_id), ivalbin(customer_id) from v_sales group by customer_id;


customer_id ivalbin(item_id) ivalbin(customer_id)



C1 6144 2048
C2 6144 4096

select customer_id, p(item_id), p(customer_id) from v_sales group by customer_id;


customer_id

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

select item_id,sales#item_id,items#item_id from v_sales;


item_id sales#item_id items#item_id



artA artA artA
artB artB artB
artB artB artB
artB artB artB
artA artA artA
artA artA artA
# artC #

select count(*) from v_sales and item_id='artC';


count(*)

select count(*) from v_sales and items#item_id='artC';


count(*)

select count(*) from v_sales and sales#item_id='artC';


count(*)



1

escape quote

''

insert into item_tags values('artD','A''__B');


reponse



success

refresh dirty view;


reponse



success

select * from v_item_tags where item_id='artD';


item_id tag



artD A'__B

select * from v_item_tags where tag='A''__B';


item_id tag



artD A'__B

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

--utility functions list
desc function callback where(2,'utility');


function_name function_type signature comment



ratio utility ratio( value1, value2) returns value1/value2 or 0 in limit cases (division by zero, nil value)
round utility round( v , n_digits ) returns math.floor(v+0.5), with n_digits precision, n_digits defaults to 0
decode utility decode( x , y , a , b ) returns a in x equals y , b otherwise
least utility least( x , y ) checks that x and y are numbers and returns least
greatest utility greatest( x , y ) checks that x and y are numbers and returns greatest
ceil utility ceil( x ) checks that x is number and calls math.ceil
floor utility floor( x ) checks that x is number and calls math.floor
check utility check( cond ) boolean function
instr utility instr( str1 , str2 ) calls string.find(str1, str2), returns -1 if not found
 length utility  length( tbl ) return numbers of key of a key/value table
merge utility merge( tbl1, tbl2 ) sums the values of 2 key/value tables
stringify utility stringify( tbl ) stringify a key/value table
join utility join(idx_values, arr_fields, _sep, _type, fn) joins an array _sep, _type and fn are optional
concat utility concat( a , b, c, d, e, f ) returns a .. nvl(b,"") .. nvl(c,"") .. nvl(d,"") .. nvl(e,"") .. nvl(f,"")
nvl utility nvl( a , b ) returns b if a==nil

callback Lua functions

--callback functions list
desc function callback where(2,'callback');


function_name function_type signature comment



add_number callback add_number( name, str_fn ) adds a number column to resultset
add_text callback add_text( name, str_fn ) adds a text column to resultset
select_from callback select_from( 'group(<column_pos/column_name>) <sum/count/avg/min/max>(<column_pos/column_name>)' ) does a select ... from (the_resultset)
sub_select callback sub_select( 'group(<column_pos/column_name>) <sum/count/avg/min/max>(<column_pos/column_name>)' ) adds a sub select column
where callback where( <column_pos/column_name> , <'value'/{'v1''v2'...}> ) does an "equal where clause" on the resultset
where_not callback where_not( <column_pos/column_name> , <'value'/{'v1''v2'...}> ) does an "not equal where clause" on the resultset
like callback like( <column_pos/column_name> , 'value1!value2' ) does a "like where clause" on the resultset
grep callback grep( 'value1!value2' ) does a "like where clause" on the resultset
limit callback limit( rownum ) limits the resultset to rownum lines
sort callback sort( <column_pos1/column_name1> , <'asc'/'desc'> , <column_pos2/column_name2> , ... ) sorts the resultset
export callback export( path/to/file , header_yn )
open_join callback open_join( 'with1,with2..' , 'join_col1,join_col2..' , 'new_with_name') do an open join on withs
rename callback rename( 'col_name' , 'new_col_name' ) rename a column
keep_columns callback keep_columns( 'col1,col2...' ) keeps column list

select item_id,customer_id,sum(sales_qty) from v_sales group by item_id,customer_id callback sort(3,'asc');


item_id customer_id sum(sales_qty)



artB C1 4
artA C2 5
# C1 8
artA C1 10
artB C2 13

select item_id,sum(sales_qty) from v_sales group by item_id callback sort(2,'asc');


item_id sum(sales_qty)



# 8
artA 15
artB 17

select customer_id,sum(sales_qty) from v_sales group by customer_id callback sort(2,'asc');


customer_id sum(sales_qty)



C2 18
C1 22

select item_id,customer_id,sales_qty from v_sales
callback select_from('group(1) group(2) sum(3)') sort(3,'asc');


group_item_id group_customer_id sum_sales_qty



artB C1 4
artA C2 5
# C1 8
artA C1 10
artB C2 13

select item_id,customer_id,sales_qty from v_sales
callback select_from('group(1) sum(3)') sort(2,'asc');


group_item_id sum_sales_qty



# 8
artA 15
artB 17

select item_id,customer_id,sales_qty from v_sales
callback select_from('group(2) sum(3)') sort(2,'asc');


group_customer_id sum_sales_qty



C2 18
C1 22

select item_id,customer_id,sales_qty from v_sales
callback sub_select('group(1) group(2) sum(3)');


item_id customer_id sales_qty group_item_id group_customer_id sum_sales_qty



artA C1 5 artA C1 10
artB C2 6 artB C2 13
artB C1 4 artB C1 4
artB C2 7 artB C2 13
artA C1 5 artA C1 10
artA C2 5 artA C2 5
# C1 8 # C1 8

select item_id,customer_id,sales_qty from v_sales
callback sub_select('group(2) sum(3)');


item_id customer_id sales_qty group_customer_id sum_sales_qty



artA C1 5 C1 22
artB C2 6 C2 18
artB C1 4 C1 22
artB C2 7 C2 18
artA C1 5 C1 22
artA C2 5 C2 18
# C1 8 C1 22

select sales_date, dept, sum(sales_qty) from v_sales;


sales_date dept sum(sales_qty)



20191231 dept #1 15
20200102 dept #2 13
20191231 dept #2 4
20200102 # 8

select dept, sum(sales_qty) from v_sales;


dept sum(sales_qty)



dept #1 15
dept #2 17
# 8

select sum(sales_qty) from v_sales;


sum(sales_qty)



40

with
a as(select sales_date, dept, sum(sales_qty) as sales_qty from v_sales),
b as(select dept, sum(sales_qty) as dept_sales_qty from v_sales),
c as(select sum(sales_qty) as tot_sales_qty from v_sales),
select from no_view, * function fn_nothing
cb
open_join('a,b','dept','a_b')
open_join('a_b,c')
add_number('dept_share','return round(line.sales_qty/line.dept_sales_qty*100,3)')
add_number('total_share','return line.sales_qty/line.tot_sales_qty*100')
keep_columns('sales_date,dept,sales_qty,dept_sales_qty,dept_share,total_share')
;


sales_date dept sales_qty dept_sales_qty dept_share total_share



20200102 # 8 8 100 20
20200102 dept #2 13 17 76.471 32.500
20191231 dept #2 4 17 23.529 10
20191231 dept #1 15 15 100 37.500

data model modification #1

...

  create big table sales( item_id t_item_id, customer_id t_customer_id, sales_date text, sales_qty number, line_id text, packaging_id t_item_id, sales_qty2 number);


reponse



success

--commit changes
refresh dirty view;


reponse



success

select sales.* from v_sales;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artB C2 20200102 6 ID02 # 0
artB C1 20191231 4 ID03 # 0
artB C2 20200102 7 ID04 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0
# C1 20200102 8 ID05 # 0

insert into sales values('artA','C2','20191231',5,'ID09','',55);


reponse



success

refresh dirty view;


reponse



success

select sales.* from v_sales;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artB C2 20200102 6 ID02 # 0
artB C1 20191231 4 ID03 # 0
artB C2 20200102 7 ID04 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0
# C1 20200102 8 ID05 # 0
artA C2 20191231 5 ID09 # 55

data model modification #2

Since v1.17.08 SB is a lot more flexible

create col_type t_a as text;


reponse



success

create col_type t_c as text;


reponse



success

create merge table dim_a (a t_a, aa text);


reponse



success

create big table foo (a t_a, b number, c text);


reponse



success

create view v_foo as select * from foo, dim_a where foo.a=dim_a.a;


reponse



success

refresh dirty view;


reponse



success

insert into foo values('a1',1,'c1');


reponse



success

insert into foo values('a2',2,'c2');


reponse



success

insert into dim_a values('a1','aa1')('a2','aa2')('a3','aa3');


reponse



success

refresh dirty view;


reponse



success

select * from v_foo;


a b c a aa



a1 1 c1 a1 aa1
a2 2 c2 a2 aa2

--col_type change --> forbidden
continue_on_error(121);


reponse



success

create big table foo (a t_a, b number, c t_c);


reponse



error 121 (continue): changing column type during table alter is not allowed

--col position change and/or add column --> ok
create big table foo (c text, a t_a, b number, d text);


reponse



success

desc table cb grep('foo');


table_name column_count line_count has_delete has_update parent_view_hidden



foo 3 2 n n n
__NEW_foo__ 4 0 n n n

desc view cb grep('foo');


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count



v_foo 2 2 y n n 10 0 12 12 0 0

--view must be recreated because joined columns position have changed, hence the error here under
continue_on_error(43);


reponse



success

refresh dirty view;


reponse



error 43 (continue): joined columns must have same column type

desc view cb grep('foo');


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count



v_foo 2 2 y n n 10 0 12 12 0 0

continue_on_error(1);


reponse



success

create view v_foo as select * from foo, dim_a where foo.a=dim_a.a;


reponse



success

refresh dirty view;


reponse



success

desc table cb grep('foo');


table_name column_count line_count has_delete has_update parent_view_hidden



foo 4 2 n n n

desc view cb grep('foo');


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count



v_foo 2 2 n n n 10 0 12 12 0 0

select * from v_foo;


c a b d a aa



c1 a1 1 NO_DATA a1 aa1
c2 a2 2 NO_DATA a2 aa2

insert into foo values('c3','a3',3,'d3');


reponse



success

insert into foo values('c4','a4',4,'d4');


reponse



success

refresh dirty view;


reponse



success

select * from v_foo;


c a b d a aa



c1 a1 1 NO_DATA a1 aa1
c2 a2 2 NO_DATA a2 aa2
c3 a3 3 d3 a3 aa3
c4 # 4 d4 # #

create big table foo (c text, d text, b number);


reponse



success

create view v_foo as select * from foo;


reponse



success

refresh dirty view;


reponse



success

select * from v_foo;


c d b



c1 NO_DATA 1
c2 NO_DATA 2
c3 d3 3
c4 d4 4

--

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'

select * from v_sales and dept='dept #1';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 0 artA the article A dept #1 10 1.500 C2 customer #2
artA C2 20191231 5 ID09 # 55 artA the article A dept #1 10 1.500 C2 customer #2

--SB allows you to use "and" instead of where, bacause it is easier
select * from v_sales and dept='dept #1';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 0 artA the article A dept #1 10 1.500 C2 customer #2
artA C2 20191231 5 ID09 # 55 artA the article A dept #1 10 1.500 C2 customer #2

select * from v_sales and dept in ('dept #1');


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 0 artA the article A dept #1 10 1.500 C2 customer #2
artA C2 20191231 5 ID09 # 55 artA the article A dept #1 10 1.500 C2 customer #2

select * from v_sales and dept nin ('dept #2');


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 0 artA the article A dept #1 10 1.500 C2 customer #2
# C1 20200102 8 ID05 # 0 # # # 0 0 C1 customer #1
artA C2 20191231 5 ID09 # 55 artA the article A dept #1 10 1.500 C2 customer #2

--like is not case sensitive
select * from v_sales and dept like 'DePt #1';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 0 artA the article A dept #1 10 1.500 C2 customer #2
artA C2 20191231 5 ID09 # 55 artA the article A dept #1 10 1.500 C2 customer #2

--

where clause / Lua boolean function

any Lua boolean function can be used in a where clause using one and only one column

--boolean functions list
desc function callback where(2,'boolean');


function_name function_type signature comment



regex boolean regex( str , pattern ) returns true is if string.match( str , pattern ) (upperbase comparaison) more info http://www.lua.org/manual/5.2/manual.html#6.4.1
is_not boolean is_not( boolean_value ) returns not boolean_value
regex_or boolean function regex_or( str , pattern, pattern2, ...) returns regex( str , pattern ) or regex( str , pattern2 ) ...
btw boolean btw( x , y , z ) returns true  if y < x and x < z then (number comparaison)
btwe boolean btwe( x , y , z ) returns true  if y <= x and x <= z then (number comparaison)
btwe_s boolean btwe_s( x , y , z ) returns true  if y <= x and x <= z then (string comparaison)
nbtwe boolean nbtwe( x , y , z ) not between or equal, returns false if y <= x and x <= z
btwe_or boolean function btwe_or( x , y , z , y2 , z2 ... ) returns btwe( x , y , z ) or btwe( x , y2 , z2 )
nbtwe_and boolean function nbtwe_and( x , y , z , y2 , z2 ... ) returns nbtwe( x , y , z ) and nbtwe( x , y2 , z2 )
gt boolean gt( x , y ) greater than, returns true is x > y (number comparaison)
gte boolean gte_( x , y ) greater than or equal, returns true is x >= y (number comparaison)
lt boolean lt( x , y ) lower than, returns true is x < y (number comparaison)
lte boolean lte( x , y ) lower than or equal, returns true is x <= y (number comparaison)
gt_s boolean gt_s( x , y ) greater than, returns true is x > y (string comparaison)
gte_s boolean gte_s( x , y ) greater than or equal, returns true is x >= y (string comparaison)
lt_s boolean lt_s( x , y ) lower than, returns true is x < y (string comparaison)
lte_s boolean lte_s( x , y ) lower than or equal, returns true is x <= y (string comparaison)
e boolean e( x , y ) equal, returns true is x==y
ne boolean ne( x , y ) not equal, returns true is x!=y
isin boolean isin( x , { val1, val2, .. } ) is in
isnin boolean isnin( x , { val1, val2, .. } ) is not in
mod boolean mod( x , y , z ) (x % y) == z

--

(not)exists basics

select .. from view1
and 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

--
--
--exists
select sales.* from v_sales
where v_item_tags.tag='tag #1';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0
artA C2 20191231 5 ID09 # 55

--not exists
select sales.* from v_sales
where v_item_tags!.tag='tag #1';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artB C2 20200102 6 ID02 # 0
artB C1 20191231 4 ID03 # 0
artB C2 20200102 7 ID04 box1 0
# C1 20200102 8 ID05 # 0

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! ..

select item_id, dept from v_items cb sort(1,'asc');


item_id dept



artA dept #1
artB dept #2
box1 packaging

--sales in dept #1
with
a as(select item_id, count(item_id) from v_items where dept='dept #1' group by item_id)
select sales.* from v_sales, a
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0
artA C2 20191231 5 ID09 # 55

--sales not in dept #1
with
a as(select item_id, count(item_id) from v_items where dept='dept #1' group by item_id)
select sales.* from v_sales, a!
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artB C2 20200102 6 ID02 # 0
artB C1 20191231 4 ID03 # 0
artB C2 20200102 7 ID04 box1 0
# C1 20200102 8 ID05 # 0

--using alias
--if the alias can't be found, the "first column with same col_type" rule applies
with
a as(select item_id as columm_that_does_not_exists, count(item_id) from v_items where dept='dept #1' group by item_id)
select sales.* from v_sales, a
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0
artA C2 20191231 5 ID09 # 55

--no packaging in dept #1
with
a as(select item_id as packaging_id, count(item_id) from v_items where dept='dept #1' group by item_id)
select sales.* from v_sales, a
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2

--sales of items having packaging in dept packaging
with
a as(select item_id as packaging_id, count(item_id) from v_items where dept='packaging' group by item_id)
select sales.* from v_sales, a
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artB C2 20200102 7 ID04 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0

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_name2

select item_id, dept, customer_id, sales_qty from v_sales;


item_id dept customer_id sales_qty



artA dept #1 C1 5
artB dept #2 C2 6
artB dept #2 C1 4
artB dept #2 C2 7
artA dept #1 C1 5
artA dept #1 C2 5
# # C1 8
artA dept #1 C2 5

select * from v_item_tags;


item_id tag



artA tag #1
artA tag #2
artD A'__B

--in which other departments do customers that buy from dept #1, buy from
--the group of customers is "customers that buy from dept #1"
with
group_of_customers as(select customer_id, count(customer_id) from v_sales and dept='dept #1' group by customer_id)
select dept,count(*),list(customer_id) from v_sales, group_of_customers and dept nin ('dept #1',null) group by dept
;


dept count(*) list(customer_id)



dept #2 3 C1,C2

select customer_id, count(customer_id) from v_sales and dept='dept #1' group by customer_id
;


customer_id count(customer_id)



C1 2
C2 2

with
group_of_customers as(select customer_id, count(customer_id) from v_sales and dept='dept #1' group by customer_id)
select sales.* from v_sales, group_of_customers and dept nin ('dept #1',null)
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artB C2 20200102 6 ID02 # 0
artB C1 20191231 4 ID03 # 0
artB C2 20200102 7 ID04 box1 0

--sames thing but my group of customers is "customers that buy from dept #1" and "buy item with tag #1"
with
items_with_tag1 as(select item_id, count(item_id) from v_item_tags and tag='tag #1' group by item_id),

  group_of_customers as(select customer_id, count(customer_id) from v_sales, items_with_tag1 and dept='dept #1' group by customer_id)

select dept,count(*),list(customer_id) from v_sales, group_of_customers and dept nin ('dept #1',null) group by dept
;


dept count(*) list(customer_id)



dept #2 3 C1,C2

permissions

end_user col_type

create table article_permission ( user_name end_user, art_id t_item_id);


reponse



success

insert into article_permission values('rd','artA');


reponse



success

insert into article_permission values('pt','artA');


reponse



success

insert into article_permission values('pt','artB');


reponse



success

create view v_article_permission as select * from article_permission;


reponse



success

create table customer_permission ( user_name end_user, customer_id t_customer_id);


reponse



success

insert into customer_permission values('rd','C1');


reponse



success

insert into customer_permission values('pt','*');


reponse



success

create view v_customer_permission as select * from customer_permission;


reponse



success

refresh dirty view;


reponse



success

select * from v_sales where end_user='pt';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artB C2 20200102 6 ID02 # 0 artB the article B dept #2 10 3.200 C2 customer #2
artB C1 20191231 4 ID03 # 0 artB the article B dept #2 10 3.200 C1 customer #1
artB C2 20200102 7 ID04 box1 0 artB the article B dept #2 10 3.200 C2 customer #2
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C2 20191231 5 ID07 box1 0 artA the article A dept #1 10 1.500 C2 customer #2
artA C2 20191231 5 ID09 # 55 artA the article A dept #1 10 1.500 C2 customer #2

select * from v_sales where end_user='rd';


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2 item_id art_label dept avg_week_sales sales_price customer_id customer_name



artA C1 20191231 5 ID01 box1 0 artA the article A dept #1 10 1.500 C1 customer #1
artA C1 20191231 5 ID06 box1 0 artA the article A dept #1 10 1.500 C1 customer #1

dynamic sql (advanced)

set log_verbose='n';


reponse



success

--`#LOAD#..` and custom callbacks
-- this sql
select count(*) from v_sales
cb add_text('foo','return "hello world"')
;


count(*) foo



8 hello world

-- can be written this way using Lua load, usefull when you write complex callbacks
select count(*) from v_sales
cb add_text('foo','return fn()')
`#LOAD#
function fn()
return "hello world"
end
`
;


count(*) foo



8 hello world

--`#LOAD#..` and `#DYN_SQL#..`
-- this sql
select count(*) from v_sales
and item_id='artA'
;


count(*)



4

-- can be written this way with a "dynamic sql" approach
select count(*) from v_sales
`#DYN_SQL#fn_where_clause()`
`#LOAD#
function fn_where_clause()
return "and item_id='artA'"
end
`
;


count(*)



4

-- stormbase_replay1.sql and stormbase_replay2.sql
-- when you write dynamic code, at some point you will want to see the "final sql" and maybe replay it for debugging
-- replay files and parameter generate_replay should be used to achieve this
system 'rm -f stormbase_replay*.sql';


reponse

set generate_replay='y';


reponse



success

-- this sql has dynamic code and complex custom callbacks
select count(*) from v_sales
`#DYN_SQL#fn_where_clause()`
cb add_text('foo','return fn()')
`#LOAD#
function fn_where_clause()
return "and item_id='artA'"
end
function fn()
return "hello world"
end
`
;


count(*) foo



4 hello world

-- stormbase_replay1.sql contains the initial sql text
system 'cat stormbase_replay1.sql';


reponse



select count(*) from v_sales
`#DYN_SQL#fn_where_clause()`
cb add_text('foo','return fn()')
`#LOAD#
function fn_where_clause()
  return "and item_id='artA'"
end
function fn()
  return "hello world"
end
`
;
system 'cat stormbase_replay1.sql';

-- stormbase_replay2.sql contains the sql after
-- `#LOAD#..` have been loaded in session's context, and have been removed from stormbase_replay2.sql
-- `#LOAD_KEEP#..` have been loaded in session's context, but have *NOT* been removed from stormbase_replay2.sql
-- `#DYN_SQL#..` have been evaluated and replaced in stormbase_replay2.sql
system 'cat stormbase_replay2.sql';


reponse



select count(*) from v_sales
and item_id='artA'
cb add_text('foo','return fn()')
;
system 'cat stormbase_replay1.sql';
system 'cat stormbase_replay2.sql';

system 'rm -f stormbase_replay*.sql';


reponse

-- so when to use LOAD vs LOAD_KEEP ?

  -- it is just a matter of being able to replay, so if sql in stormbase_replay2.sql needs the lua code I must use LOAD_KEEP

-- so this sql would be better this way, even though in terms of execution it is the same
select count(*) from v_sales
`#DYN_SQL#fn_where_clause()`
cb add_text('foo','return fn()')
`#LOAD#
function fn_where_clause()
return "and item_id='artA'"
end
`
`#LOAD_KEEP#
function fn()
return "hello world"
end
`
;


count(*) foo



4 hello world

--this sql can be replayed and does not content useless code
system 'cat stormbase_replay2.sql';


reponse



select count(*) from v_sales
and item_id='artA'
cb add_text('foo','return fn()')
`#LOAD_KEEP#
function fn()
  return "hello world"
end
`
;
system 'cat stormbase_replay2.sql';

-- comments are allowed in dynamic sql
system 'rm -f stormbase_replay*.sql';


reponse

select count(*) from v_sales
`#DYN_SQL#fn_where_clause()`
`#LOAD#
function fn_where_clause()
return "-- and item_id='artA'"
end
`
;


count(*)



8

system 'cat stormbase_replay2.sql';


reponse



select count(*) from v_sales
-- and item_id='artA'
;
system 'cat stormbase_replay2.sql';

-- with query, "fn_nothing" syntax (small number of lines)
with
a as (select sales.* from v_sales),
select from no_view,* function fn_nothing
cb union('a')
;


item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



artA C1 20191231 5 ID01 box1 0
artB C2 20200102 6 ID02 # 0
artB C1 20191231 4 ID03 # 0
artB C2 20200102 7 ID04 box1 0
artA C1 20191231 5 ID06 box1 0
artA C2 20191231 5 ID07 box1 0
# C1 20200102 8 ID05 # 0
artA C2 20191231 5 ID09 # 55

-- using results from previous with
-- v_max_sales_qty is a lua global variable, it can be "called" in the sql using `the_variable`
-- note the tonumber, SB with inject variable with quotes ('8' in this case, hence the need of tonumber)
-- note also that number_column=value can't be used in SB, use e(number_column,value) instead
with
a as (select max(sales_qty) as max_sales_qty from v_sales cb add_text('foo','return fn(max_sales_qty)')),
b as (select sales.* from v_sales and e(sales_qty,tonumber(`v_max_sales_qty`))),
select from no_view,* function fn_nothing
cb open_join('a,b')
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function fn(n)
v_max_sales_qty=n
return "done"
end
`
;


max_sales_qty foo item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



8 done # C1 20200102 8 ID05 # 0

-- `#DYN_SQL#..` vs `DYN_SQL#..`
-- the same can be done with a dynamic sql syntax
-- `#DYN_SQL#..` can't be used because it is evaluted before sql execution
-- `DYN_SQL#..` (w/o #) should be used instead because it is evaluated before each "with sql" execution
-- therefore results of `DYN_SQL#..` are visible in stormbase.log but not in replay files
with

  a as (select max(sales_qty) as max_sales_qty from v_sales cb add_text('foo','v_max_sales_qty=max_sales_qty return "foo"')),

b as (`DYN_SQL#fn_sql()`),
select from no_view,* function fn_nothing
cb open_join('a,b')
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function fn_sql()
return "select sales.* from v_sales and e(sales_qty,"..v_max_sales_qty..")"
end
`
;


max_sales_qty foo item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



8 foo # C1 20200102 8 ID05 # 0

-- note that comments are not allowed in `DYN_SQL#..`
continue_on_error(151);


reponse



success

with

  a as (select max(sales_qty) as max_sales_qty from v_sales cb add_text('foo','v_max_sales_qty=max_sales_qty return "foo"')),

b as (`DYN_SQL#fn_sql()`),
select from no_view,* function fn_nothing
cb open_join('a,b')
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function fn_sql()
return "select sales.* from v_sales \n--and e(sales_qty,"..v_max_sales_qty..")"
end
`
;


reponse



error 151 (continue): select on secondary view failed

stop_on_error;


reponse



success

-- debug function
-- you may use lua debug function (taking one string parameter) do display thing in file stormbase.debug
system 'rm -f stormbase.debug';


reponse

with

  a as (select max(sales_qty) as max_sales_qty from v_sales cb add_text('foo','v_max_sales_qty=max_sales_qty return "foo"')),

b as (`DYN_SQL#fn_sql()`),
select from no_view,* function fn_nothing
cb open_join('a,b')
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function fn_sql()
  debug("lua variable v_max_sales_qty value is "..v_max_sales_qty)
  debug("lua variable v_max_sales_qty type is "..type(v_max_sales_qty))
  return "select sales.* from v_sales and e(sales_qty,"..v_max_sales_qty..")"
end
`
;


max_sales_qty foo item_id customer_id sales_date sales_qty line_id packaging_id sales_qty2



8 foo # C1 20200102 8 ID05 # 0

system 'cat stormbase.debug';


reponse



lua variable v_max_sales_qty value is 8
lua variable v_max_sales_qty type is number

-- error 222 and file stormbase.debug
-- if your lua code fails during parsing or during execution, stormbase will return an error 222
-- usefull debuging information will also be displayed in file stormbase.debug
continue_on_error(222);


reponse



success

system 'rm -f stormbase.debug';


reponse

with

  a as (select max(sales_qty) as max_sales_qty from v_sales cb add_text('foo','v_max_sales_qty=max_sales_qty return "foo"')),

b as (`DYN_SQL#fn_sql()`),
select from no_view,* function fn_nothing
cb open_join('a,b')
`#LOAD_KEEP#
v_max_sales_qty="no_set"
function fn_sql()
  -- this will fail
  local foo=nil..""
  return "select sales.* from v_sales and e(sales_qty,"..v_max_sales_qty..")"
end
`
;


reponse



error 222 (continue): error in dynamic sql (see file stormbase.debug for more info)

stop_on_error;


reponse



success

-- message "[string "..."]:5: attempt to concatenate a nil value" tells you to look at line #5 in lua load section
system 'cat stormbase.debug';


reponse



<<<
-- error:
[string "..."]:5: attempt to concatenate a nil value
--
-- lua call:
return fn_sql()
--
-- lua load:
1. 
2. v_max_sales_qty="no_set"
3. function fn_sql()
4.   -- this will fail
5.   local foo=nil..""
6.   return "select sales.* from v_sales and e(sales_qty,"..v_max_sales_qty..")"
7. end
8. 
--
>>>

-- cache management
-- since v1.17.10_F28 cache is based on the original sqk, so there is nothing to worry about when you do dynamic sql
set cache='y';


reponse



success

-- sql is executed because it is new
with a as(`DYN_SQL#fn_sql()`),
--AA
select from no_view,* function fn_nothing
cb union('a')
`#LOAD#
function fn_sql()
return "select unique(item_id) from v_sales and item_id='artA'"
end
`
;


unique(item_id)



artA

-- sql is executed because the `#LOAD#..` has changed
with a as(`DYN_SQL#fn_sql()`),
--BB
select from no_view,* function fn_nothing
cb union('a')
`#LOAD#
function fn_sql()
return "select unique(item_id) from v_sales and item_id='artB'"
end
`
;


unique(item_id)



artB

save;


reponse



success

-- ###########################
-- RUNNING shutdown.sql
shutdown;

function_name	function_type	signature	comment
ratio	utility	ratio( value1, value2)	returns value1/value2 or 0 in limit cases (division by zero, nil value)
round	utility	round( v , n_digits )	returns math.floor(v+0.5), with n_digits precision, n_digits defaults to 0
decode	utility	decode( x , y , a , b )	returns a in x equals y , b otherwise
least	utility	least( x , y )	checks that x and y are numbers and returns least
greatest	utility	greatest( x , y )	checks that x and y are numbers and returns greatest
ceil	utility	ceil( x )	checks that x is number and calls math.ceil
floor	utility	floor( x )	checks that x is number and calls math.floor
check	utility	check( cond )	boolean function
instr	utility	instr( str1 , str2 )	calls string.find(str1, str2), returns -1 if not found
length	utility	length( tbl )	return numbers of key of a key/value table
merge	utility	merge( tbl1, tbl2 )	sums the values of 2 key/value tables
stringify	utility	stringify( tbl )	stringify a key/value table
join	utility	join(idx_values, arr_fields, _sep, _type, fn)	joins an array _sep, _type and fn are optional
concat	utility	concat( a , b, c, d, e, f )	returns a .. nvl(b,"") .. nvl(c,"") .. nvl(d,"") .. nvl(e,"") .. nvl(f,"")
nvl	utility	nvl( a , b )	returns b if a==nil

function_name	function_type	signature	comment
add_number	callback	add_number( name, str_fn )	adds a number column to resultset
add_text	callback	add_text( name, str_fn )	adds a text column to resultset
select_from	callback	select_from( 'group(<column_pos/column_name>) <sum/count/avg/min/max>(<column_pos/column_name>)' )	does a select ... from (the_resultset)
sub_select	callback	sub_select( 'group(<column_pos/column_name>) <sum/count/avg/min/max>(<column_pos/column_name>)' )	adds a sub select column
where	callback	where( <column_pos/column_name> , <'value'/{'v1''v2'...}> )	does an "equal where clause" on the resultset
where_not	callback	where_not( <column_pos/column_name> , <'value'/{'v1''v2'...}> )	does an "not equal where clause" on the resultset
like	callback	like( <column_pos/column_name> , 'value1!value2' )	does a "like where clause" on the resultset
grep	callback	grep( 'value1!value2' )	does a "like where clause" on the resultset
limit	callback	limit( rownum )	limits the resultset to rownum lines
sort	callback	sort( <column_pos1/column_name1> , <'asc'/'desc'> , <column_pos2/column_name2> , ... )	sorts the resultset
export	callback	export( path/to/file , header_yn )
open_join	callback	open_join( 'with1,with2..' , 'join_col1,join_col2..' , 'new_with_name')	do an open join on withs
rename	callback	rename( 'col_name' , 'new_col_name' )	rename a column
keep_columns	callback	keep_columns( 'col1,col2...' )	keeps column list

function_name	function_type	signature	comment
regex	boolean	regex( str , pattern )	returns true is if string.match( str , pattern ) (upperbase comparaison) more info http://www.lua.org/manual/5.2/manual.html#6.4.1
is_not	boolean	is_not( boolean_value )	returns not boolean_value
regex_or	boolean	function regex_or( str , pattern, pattern2, ...)	returns regex( str , pattern ) or regex( str , pattern2 ) ...
btw	boolean	btw( x , y , z )	returns true if y < x and x < z then (number comparaison)
btwe	boolean	btwe( x , y , z )	returns true if y <= x and x <= z then (number comparaison)
btwe_s	boolean	btwe_s( x , y , z )	returns true if y <= x and x <= z then (string comparaison)
nbtwe	boolean	nbtwe( x , y , z )	not between or equal, returns false if y <= x and x <= z
btwe_or	boolean	function btwe_or( x , y , z , y2 , z2 ... )	returns btwe( x , y , z ) or btwe( x , y2 , z2 )
nbtwe_and	boolean	function nbtwe_and( x , y , z , y2 , z2 ... )	returns nbtwe( x , y , z ) and nbtwe( x , y2 , z2 )
gt	boolean	gt( x , y )	greater than, returns true is x > y (number comparaison)
gte	boolean	gte_( x , y )	greater than or equal, returns true is x >= y (number comparaison)
lt	boolean	lt( x , y )	lower than, returns true is x < y (number comparaison)
lte	boolean	lte( x , y )	lower than or equal, returns true is x <= y (number comparaison)
gt_s	boolean	gt_s( x , y )	greater than, returns true is x > y (string comparaison)
gte_s	boolean	gte_s( x , y )	greater than or equal, returns true is x >= y (string comparaison)
lt_s	boolean	lt_s( x , y )	lower than, returns true is x < y (string comparaison)
lte_s	boolean	lte_s( x , y )	lower than or equal, returns true is x <= y (string comparaison)
e	boolean	e( x , y )	equal, returns true is x==y
ne	boolean	ne( x , y )	not equal, returns true is x!=y
isin	boolean	isin( x , { val1, val2, .. } )	is in
isnin	boolean	isnin( x , { val1, val2, .. } )	is not in
mod	boolean	mod( x , y , z )	(x % y) == z

< * | *** >

Basics

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)