<  *  | *** >

Computed columns 

  • Basic types using loop function fn_computed_col
  • SB parameter
  • SB C interface
  • priority between computed column and real column
  • computed column basics
  • computed columns with analytic function
  • computed columns with param
  • optimized contexts (type A)
  • optimized contexts (type B)
  • using other columns
  • using other columns (better code)
  • SB predefined contexts C API
  • using context basics
  • using custom context
  • using indexes on computed columns

-- ###########################
-- RUNNING stop_on_error.sql
stop_on_error;
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

create col_type t_dept_id as text;
reponse
success

create col_type t_item_id as text;
reponse
success

create col_type t_customer_id as text;
reponse
success

create col_type t_date as text;
reponse
success

create col_type t_customer_info as text;
reponse
success

create col_type end_user as text;
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

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

create table item_tags( item_id t_item_id, tag text);
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

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

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

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

create view v_items as select * from items;
reponse
success

create view v_item_tags as select * from item_tags;
reponse
success

create view v_fidelity_cards as select * from fidelity_cards;
reponse
success

create view v_item_customer_infos as select * from item_customer_infos;
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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

refresh dirty view;
reponse
success

-- ###########################
-- RUNNING doc_computed_columns.sql
--

Computed columns

Basic types using loop function fn_computed_col

No coding an no parameter is necessary in 2 cases (most common cases):
1/ text 1 to 1 relationship, get the department of an item (type A)
2/ coefficient 1 to 1 relationship, get price from qty using item price (type B)
desc table items;
table_namecolumn_namecolumn_typecol_type_name
itemsitem_idtextt_item_id
itemsart_labeltextsys#type#items#art_label
itemsdepttextt_dept_id
itemsavg_week_salesnumbersys#type#items#avg_week_sales
itemssales_pricenumbersys#type#items#sales_price

desc table sales;
table_namecolumn_namecolumn_typecol_type_name
salesitem_idtextt_item_id
salescustomer_idtextt_customer_id
salessales_datetextt_date
salessales_qtynumbersys#type#sales#sales_qty
salesline_idtextsys#type#sales#line_id
salespackaging_idtextt_item_id

select item_id,dept,sales_price from v_items cb sort(1,'asc');
item_iddeptsales_price
artAdept #11.500
artBdept #23.200
box1packaging0

--type A
loop items(item_id,dept) function fn_computed_col('get_dept','A');
LOOP_RESULT
DONE

refresh computed_column;
reponse
success

--no parameter defaults to item_id (first column of loop)
select line_id,item_id,packaging_id,get_dept() from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idget_dept()
ID01artAbox1dept #1
ID02artB#dept #2
ID03artB#dept #2
ID04artBbox1dept #2
ID05###
ID06artAbox1dept #1
ID07artAbox1dept #1

--using reverse index
select line_id,item_id,packaging_id,get_dept() from v_sales and get_dept()='dept #1' cb sort(1,'asc');
line_iditem_idpackaging_idget_dept()
ID01artAbox1dept #1
ID06artAbox1dept #1
ID07artAbox1dept #1

select line_id,item_id,packaging_id,get_dept(item_id) from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idget_dept(item_id)
ID01artAbox1dept #1
ID02artB#dept #2
ID03artB#dept #2
ID04artBbox1dept #2
ID05###
ID06artAbox1dept #1
ID07artAbox1dept #1

select line_id,item_id,packaging_id,get_dept(packaging_id) from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idget_dept(packaging_id)
ID01artAbox1packaging
ID02artB##
ID03artB##
ID04artBbox1packaging
ID05###
ID06artAbox1packaging
ID07artAbox1packaging

select item_id,sales_price from v_items;
item_idsales_price
artA1.500
artB3.200
box10

--type B
loop items(item_id,sales_price) function fn_computed_col('mult_by_sales_price','B','default=2.5 toto=1');
LOOP_RESULT
DONE

loop items(item_id,sales_price) function fn_computed_col('divide_by_sales_price','B','1/X');
LOOP_RESULT
DONE

desc context;
context_name
get_deptA
mult_by_sales_priceB
divide_by_sales_priceB

refresh computed_column;
reponse
success

--no second parameter defaults to item_id (first column of loop)
select line_id,item_id,packaging_id,sales_qty,mult_by_sales_price(sales_qty) from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idsales_qtymult_by_sales_price(sales_qty)
ID01artAbox157.500
ID02artB#619.200
ID03artB#412.800
ID04artBbox1722.400
ID05##820
ID06artAbox157.500
ID07artAbox157.500

select line_id,item_id,packaging_id,sales_qty,mult_by_sales_price(sales_qty,item_id) from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idsales_qtymult_by_sales_price(sales_qty,item_id)
ID01artAbox157.500
ID02artB#619.200
ID03artB#412.800
ID04artBbox1722.400
ID05##820
ID06artAbox157.500
ID07artAbox157.500

select line_id,item_id,packaging_id,sales_qty,mult_by_sales_price(sales_qty,packaging_id) from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idsales_qtymult_by_sales_price(sales_qty,packaging_id)
ID01artAbox150
ID02artB#615
ID03artB#410
ID04artBbox170
ID05##820
ID06artAbox150
ID07artAbox150

--division instead of multiplication
select line_id,item_id,packaging_id,sales_qty,divide_by_sales_price(sales_qty) from v_sales cb sort(1,'asc');
line_iditem_idpackaging_idsales_qtydivide_by_sales_price(sales_qty)
ID01artAbox153.333
ID02artB#61.875
ID03artB#41.250
ID04artBbox172.188
ID05##80
ID06artAbox153.333
ID07artAbox153.333

SB parameter

set COMPUTED_COLUMNS='comp_col1,comp_col2,...'

SB C interface

SB needs 2 functions per computed column
1/ <comp_col_name>_before
2/ <comp_col_name>
See input/output of these functions in common.h

./_SO_CODE/common.h


typedef void (*t_fn_computed_column)(U_INT **ivals, float **num_vals, int ivals_len, void *context);

typedef struct computed_col_need {
  int needed_col_count;
  char **needed_col_names;
} COMPUTED_COL_NEED;

typedef COMPUTED_COL_NEED* (*t_fn_get_needed_columns)(char *param_fn_get_needed_columns, char **available_columns,
    int available_columns_len, char **params, int params_len);

typedef struct computed_col_prereq {

  //mandatory destination col_type (lower case)
  char *destination_col_type_name;

  //optional context that will be passed to functions
  char *needed_context_name;

  //columns needed (needed_col/fn_get_needed_columns)
  // - simple case (needed_col!=NULL) : needed_col is used if fn_get_needed_columns is NULL
  // - complex case (fn_get_needed_columns!=NULL)
  // needed_col is computed this way: needed_col=fn_get_needed_columns(param_fn_get_needed_columns,available_columns,available_columns_len)
  COMPUTED_COL_NEED *needed_col;
  char *param_fn_get_needed_columns;
  t_fn_get_needed_columns fn_get_needed_columns;

  //put your error message here, NULL means no error
  char *error;

  //y/n (default n), if computed column overloads a real column
  char priority_over_real_column_yn;

  // in simple cases this will trigger an optimized treatment in SB
  // defaults to ?
  // A means select_contex is an TYPE_A*
  // B means select_contex is an TYPE_B*
  char select_context_type; // SB_LIB_VERSION >= 9

} COMPUTED_COL_PREREQ;

typedef COMPUTED_COL_PREREQ* (*t_fn_computed_column_before)(UTILITY *U);
//returns a "select_context"
typedef void* (*t_fn_computed_column_before_select)(UTILITY *U, char **params, int params_len, void *context);
typedef void (*t_fn_computed_column_after_select)(UTILITY *U, void *select_context);

typedef struct typeA {
  U_INT *ival_needed_2_ival_dest;
  U_INT len;
} TYPE_A;
typedef TYPE_A* (*t_fn_computed_column_before_select_typeA)(UTILITY *U, char **params, int params_len, void *context);
typedef void (*t_fn_computed_column_after_select_typeA)(UTILITY *U, TYPE_A *select_context);

typedef struct typeB {
  float *ival_needed_2_coef;
  U_INT len;
} TYPE_B;
typedef TYPE_B* (*t_fn_computed_column_before_select_typeB)(UTILITY *U, char **params, int params_len, void *context);
typedef void (*t_fn_computed_column_after_select_typeB)(UTILITY *U, TYPE_B *select_context);

COMPUTED_COL_PREREQ* sb_computed_col_prereq_factory();
//

priority between computed column and real column

By default real column has priority over a computed column with same name
set COMPUTED_COLUMNS='customer_id';
reponse
success

set CPU_COUNT=1;
reponse
success

--real will be returned

./_SO_CODE/Z_doc_comp_col_not_prio.c

#include "./common.h"

COMPUTED_COL_PREREQ* customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" );
  return ret;
}

void customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  U_INT *pin_out = ivals[0];
  U_INT idept = *pin_out;
  *pin_out = idept == 10 ? NULL_IVAL : 10;
}

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_not_prio.so';
reponse
success

refresh dirty view;
reponse
success

select sales#customer_id, customer_id from v_sales;
sales#customer_idcustomer_id
C1C1
C2C2
C1C1
C2C2
C1C1
C1C1
C2C2

--computed will be returned returned
select dept,ival(dept) from v_sales group by dept;
deptival(dept)
dept #110
dept #211

./_SO_CODE/Z_doc_comp_col_prio.c

#include "./common.h"

COMPUTED_COL_PREREQ* customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" );
  ret->priority_over_real_column_yn = 'y';
  return ret;
}

void customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  U_INT *pin_out = ivals[0];
  U_INT idept = *pin_out;
  *pin_out = idept == 10 ? NULL_IVAL : 10;
}

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_prio.so';
reponse
success

refresh dirty view;
reponse
success

select sales#customer_id, customer_id from v_sales;
sales#customer_idcustomer_id
C1#
C2NO_DATA
C1NO_DATA
C2NO_DATA
C1NO_DATA
C1#
C2#

computed column basics

New_customer_id is a computed text column
Its destination col_type is t_customer_id
We are going to return always the same value
So we don't need other columns to compute new_customer_id in this basic case
Please note that needed_col_count can't be zero, because on the in/out value at first position
Hence needed_col_names=["dept"]
set COMPUTED_COLUMNS='new_customer_id';
reponse
success

set CPU_COUNT=1;
reponse
success

set SO_FILE_NAME='Z_doc_comp_col_all_null.so';
reponse
success

refresh dirty view;
reponse
success

select customer_id, ival(customer_id) from v_sales group by customer_id
callback sort(1,'asc')
;
customer_idival(customer_id)
C111
C212

select item_id, ival(item_id) from v_sales group by item_id
callback sort(1,'asc')
;
item_idival(item_id)
artA11
artB12

--new_customer_id will always be null

./_SO_CODE/Z_doc_comp_col_all_null.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1; //only dept only (not really needed)
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  *pin_out = NULL_IVAL; //update to null value
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set CPU_COUNT=1;
reponse
success

desc table;
table_namecolumn_countline_counthas_deletehas_updateparent_view_hidden
items53nnn
customers22nnn
item_tags22nnn
fidelity_cards42nnn
item_customer_infos53nnn
sales62nnn
inventory22nnn
sales_#partition#_0000162nnn
sales_#partition#_0000262nnn
sales_#partition#_0000361nnn

desc computed_column;
comp_col_nameerror
new_customer_idno error
get_deptno error
mult_by_sales_priceno error
divide_by_sales_priceno error

select new_customer_id from v_sales group by new_customer_id;
new_customer_id
#

--#SB no_index
select line_id,new_customer_id,new_customer_id from v_sales and new_customer_id=null cb sort(1,'asc');
line_idnew_customer_idnew_customer_id
ID01##
ID02##
ID03##
ID04##
ID05##
ID06##
ID07##

--#SB use_index
select line_id,new_customer_id,new_customer_id from v_sales and new_customer_id=null cb sort(1,'asc');
line_idnew_customer_idnew_customer_id
ID01##
ID02##
ID03##
ID04##
ID06##
ID07##

--new_customer_id will be equal to the first customer_id created (C1 with ival=11)

./_SO_CODE/Z_doc_comp_col_first_value.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1; //only dept only (not really needed)
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  *pin_out = 11; //update to ival 11
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_first_value.so';
reponse
success

refresh dirty view;
reponse
success

select line_id,new_customer_id from v_sales;
line_idnew_customer_id
ID01C1
ID02C1
ID03C1
ID04C1
ID05C1
ID06C1
ID07C1

computed columns with analytic function

./_SO_CODE/Z_doc_comp_col_with_pareto_col.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1; //only dept only (not really needed)
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
  needed_col->needed_col_names[0] = sb_clone("line_id" ); //pareto column
  ret->destination_col_type_name = sb_clone("sys#type#sales#line_id" ); //pareto column
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  *pin_out = 11; //update to ival 11
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_with_pareto_col.so';
reponse
success

refresh dirty view;
reponse
success

select customer_id, new_customer_id from v_sales;
customer_idnew_customer_id
C1ID02
C2ID02
C1ID02
C2ID02
C1ID02
C1ID02
C2ID02

select p(new_customer_id) from v_sales function fn_custom(
'(void* c, U_INT thread_pos, U_INT iline, int key){printf("## fn_custom ival: %d\n",key);float *f=c; *f+=1; return 0.;}',
'fn_key',
fn_num_before,fn_num_after
)
;
fn_custom ival: 11
fn_custom ival: 11
fn_custom ival: 11
fn_custom ival: 11
fn_custom ival: 11
fn_custom ival: 11
fn_custom ival: 11
num
7

select p(line_id) from v_sales function fn_custom(
'(void* c, U_INT thread_pos, U_INT iline, int key){printf("## fn_custom ival: %d\n",key);float *f=c; *f+=1; return 0.;}',
'fn_key',
fn_num_before,fn_num_after
)
;
fn_custom ival: 10
fn_custom ival: 11
fn_custom ival: 12
fn_custom ival: 13
fn_custom ival: 14
fn_custom ival: 15
fn_custom ival: 16
num
7

computed columns with param

--new_customer_id will be equal to customer_id pass in parameter (C1/ival=10 in param is A, C2/ival=11 in param is B)

./_SO_CODE/Z_doc_comp_col_with_param.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1; //only dept only (not really needed)
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" ); //not really needed
  return ret;
}

void* new_customer_id_before_select(UTILITY *U, char **params, int params_len, void *context) {
  U_INT *select_context = malloc(sizeof(U_INT));
  if (strcmp(params[0], "A" ) == 0) {
    *select_context = 10;
  } else if (strcmp(params[0], "B" ) == 0) {
    *select_context = 11;
  } else {
    *select_context = NULL_IVAL;
  }
  return select_context;
}

void new_customer_id_after_select(UTILITY *U, void *select_context) {
  free(select_context);
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  U_INT ival_context = *(U_INT*) context;
  *pin_out = ival_context;
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_with_param.so';
reponse
success

refresh dirty view;
reponse
success

select line_id, new_customer_id('A'), new_customer_id('B'), new_customer_id('C') from v_sales;
line_idnew_customer_id('A')new_customer_id('B')new_customer_id('C')
ID01NO_DATAC1#
ID02NO_DATAC1#
ID03NO_DATAC1#
ID04NO_DATAC1#
ID05NO_DATAC1#
ID06NO_DATAC1#
ID07NO_DATAC1#

select new_customer_id('A') as col1, new_customer_id('B') as col2, count(*) from v_sales group by new_customer_id('A'), new_customer_id('B');
col1col2count(*)
NO_DATAC17

select count(*) from v_sales and new_customer_id('A')='C1';
count(*)

select new_customer_id('A'),list(new_customer_id('B')) from v_sales group by new_customer_id('A');
new_customer_id('A')list(new_customer_id('B'))
NO_DATAC1

select new_customer_id('A'),count(*) from v_sales group by new_customer_id('A');
new_customer_id('A')count(*)
NO_DATA7

optimized contexts (type A)

select_context_type A
--new_customer_id will be equal to customer_id pass in parameter (C1/ival=10 in param is A, C2/ival=11 in param is B)

./_SO_CODE/Z_doc_comp_col_with_param_typeA.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 1;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("item_id" );
  needed_col->needed_col_names[0] = sb_clone("line_id" ); // ptaudou
  ret->select_context_type = 'A';
  return ret;
}

TYPE_A* new_customer_id_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) {
  TYPE_A *select_context = malloc(sizeof(TYPE_A));
  select_context->len = U->fn_get_col_type_ival_count("t_item_id" );
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#line_id" ); // ptaudou
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT));
  U_INT ival;
  if (strcmp(params[0], "A" ) == 0) {
    ival = 10;
  } else if (strcmp(params[0], "B" ) == 0) {
    ival = 11;
  } else {
    ival = NULL_IVAL;
  }
  for (int i = 0; i < select_context-> len; i++) {
    if (i == 11) {
      select_context->ival_needed_2_ival_dest[i] = ival;
    } else {
      select_context->ival_needed_2_ival_dest[i] = NULL_IVAL;
    }
  }
  return select_context;
}

void new_customer_id_after_select_typeA(UTILITY *U, U_INT *select_context) {
  free(select_context);
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  printf("## CALL new_customer_id pin_out %p, should not happen in type A\n" , pin_out);
  exit(-1);
  *pin_out = NULL_IVAL;
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_with_param_typeA.so';
reponse
success

refresh dirty view;
reponse
success

set cache='n';
reponse
success

select customer_id from v_sales;
customer_id
C1
C2
C1
C2
C1
C1
C2

select customer_id, customer_id from v_sales where customer_id='C1';
customer_idcustomer_id
C1C1
C1C1
C1C1
C1C1

select item_id, customer_id, new_customer_id('A') from v_sales;
item_idcustomer_idnew_customer_id('A')
artAC1#
artBC2NO_DATA
artBC1#
artBC2#
#C1#
artAC1#
artAC2#

select new_customer_id('A'), new_customer_id('A') from v_sales;
new_customer_id('A')new_customer_id('A')
##
NO_DATANO_DATA
##
##
##
##
##

select line_id,uniquesoft(customer_id) from v_sales group by line_id cb sort(1,'asc');
line_iduniquesoft(customer_id)
ID01C1
ID02C2
ID03C1
ID04C2
ID05C1
ID06C1
ID07C2

select line_id,uniquesoft(new_customer_id('A')) from v_sales group by line_id cb sort(1,'asc');
line_iduniquesoft(new_customer_id('A'))
ID02NO_DATA

select item_id, unique(new_customer_id('A')), max(new_customer_id('A')),count(*) from v_sales
group by item_id;
item_idunique(new_customer_id('A'))max(new_customer_id('A'))count(*)
artA##3
artBNO_DATANO_DATA3
###1

--#SB use_index explain
select new_customer_id('A'), new_customer_id('A') from v_sales and new_customer_id('A')='NO_DATA';
logs

--#SB no_index
select new_customer_id('A'), new_customer_id('A') from v_sales and new_customer_id('A')='NO_DATA';
new_customer_id('A')new_customer_id('A')
NO_DATANO_DATA

select new_customer_id('A'), new_customer_id('B'), new_customer_id('C') from v_sales;
new_customer_id('A')new_customer_id('B')new_customer_id('C')
###
NO_DATAC1#
###
###
###
###
###

select line_id, new_customer_id('A'), new_customer_id('B'), new_customer_id('C') from v_sales;
line_idnew_customer_id('A')new_customer_id('B')new_customer_id('C')
ID01###
ID02NO_DATAC1#
ID03###
ID04###
ID05###
ID06###
ID07###

select new_customer_id('A') as col1, new_customer_id('B') as col2, count(*) from v_sales group by new_customer_id('A'), new_customer_id('B');
col1col2count(*)
##6
NO_DATAC11

select count(*) from v_sales and new_customer_id('A')='C1';
count(*)

select new_customer_id('A'),list(new_customer_id('B')) from v_sales group by new_customer_id('A');
new_customer_id('A')list(new_customer_id('B'))
#
NO_DATAC1

select new_customer_id('A'),count(*) from v_sales group by new_customer_id('A');
new_customer_id('A')count(*)
#6
NO_DATA1

optimized contexts (type B)

select_context_type B
set COMPUTED_COLUMNS='new_qty,new_qty2';
reponse
success

refresh dirty view;
reponse
success

--new_customer_id will be equal to customer_id pass in parameter (C1/ival=10 in param is A, C2/ival=11 in param is B)

./_SO_CODE/Z_doc_comp_col_typeB.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_qty_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("sys#type#sales#sales_qty" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 2;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("sales_qty" );
  needed_col->needed_col_names[1] = sb_clone("item_id" );
  ret->select_context_type = 'B';
  return ret;
}

TYPE_B* new_qty_before_select_typeB(UTILITY *U, char **params, int params_len, void *context) {
  TYPE_B *select_context = malloc(sizeof(TYPE_A));
  select_context->len = U->fn_get_col_type_ival_count("t_item_id" );
  select_context->ival_needed_2_coef = malloc(select_context->len * sizeof(float));
  for (int i = 0; i < select_context-> len; i++) {
    select_context->ival_needed_2_coef[i] = i >= 10 ? 2 : -2;
  }
  return select_context;
}

void new_qty(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
}

COMPUTED_COL_PREREQ* new_qty2_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("sys#type#sales#sales_qty" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 2;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("sales_qty" );
  needed_col->needed_col_names[1] = sb_clone("line_id" );
  ret->select_context_type = 'B';
  return ret;
}

TYPE_B* new_qty2_before_select_typeB(UTILITY *U, char **params, int params_len, void *context) {
  TYPE_B *select_context = malloc(sizeof(TYPE_A));
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#line_id" );
  select_context->ival_needed_2_coef = malloc(select_context->len * sizeof(float));
  for (int i = 0; i < select_context-> len; i++) {
    select_context->ival_needed_2_coef[i] = i >= 10 ? 2 : -2;
  }
  return select_context;
}

void new_qty2(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
}

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_typeB.so';
reponse
success

refresh dirty view;
reponse
success

--using dimension ival (non pareto ival)
select item_id, ival(item_id), sum(sales_qty), sum(new_qty) from v_sales group by item_id;
item_idival(item_id)sum(sales_qty)sum(new_qty)
artA111530
artB121734
##8-16

--using dimension ival (non pareto ival)
select line_id, ival(line_id), sum(sales_qty), sum(new_qty2) from v_sales group by line_id;
line_idival(line_id)sum(sales_qty)sum(new_qty2)
ID0110510
ID0211612
ID031248
ID0413714
ID0514816
ID0615510
ID0716510

set COMPUTED_COLUMNS='new_customer_id';
reponse
success

using other columns

New_customer_id will be equal to the first customer_id will have 2 values:
1/ current customer_id if item_id is artA (ival=10)
2/ null otherwise

./_SO_CODE/Z_doc_comp_col_two_values.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 3; //customer_id and item_id are needed, we keep department for the moment
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("dept" ); //still not really needed
  needed_col->needed_col_names[1] = sb_clone("customer_id" );
  needed_col->needed_col_names[2] = sb_clone("item_id" );
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  U_INT *pival_customer_id = ivals[1]; //customer_id is passed after dept
  U_INT *pival_item_id = ivals[2]; //item_id is passed after customer_id
  if (*pival_item_id == 10) {
    *pin_out = *pival_customer_id; //update to customer_id
  } else {
    *pin_out = NULL_IVAL; //update to null value
  }
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_two_values.so';
reponse
success

refresh dirty view;
reponse
success

select line_id,customer_id,item_id from v_sales;
line_idcustomer_iditem_id
ID01C1artA
ID02C2artB
ID03C1artB
ID04C2artB
ID05C1#
ID06C1artA
ID07C2artA

select line_id,new_customer_id from v_sales;
line_idnew_customer_id
ID01#
ID02#
ID03#
ID04#
ID05#
ID06#
ID07#

select line_id,new_customer_id,customer_id from v_sales and customer_id='C2';
line_idnew_customer_idcustomer_id
ID02#C2
ID04#C2
ID07#C2

select line_id,new_customer_id from v_sales and new_customer_id='C2';
line_idnew_customer_id

using other columns (better code)

Dept was here for clarity (in/out value), let's remove it, and use customer_id instead
And we won't need to update the new in/out in case 1/ because it is already good

./_SO_CODE/Z_doc_comp_col_two_values_better.c

#include "./common.h"

COMPUTED_COL_PREREQ* new_customer_id_before(UTILITY *U) {
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory();
  ret->destination_col_type_name = sb_clone("t_customer_id" );
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED));
  ret->needed_col = needed_col;
  needed_col->needed_col_count = 2;
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*));
  needed_col->needed_col_names[0] = sb_clone("customer_id" );
  needed_col->needed_col_names[1] = sb_clone("item_id" );
  return ret;
}

void new_customer_id(U_INT **ivals, float **num_vals, int ivals_len, void *context) {
  //position 0 is "in/out"
  U_INT *pin_out = ivals[0];
  U_INT *pival_customer_id = ivals[0];
  U_INT *pival_item_id = ivals[1];
  if (*pival_item_id == 10) {
    //already good
  } else {
    *pival_customer_id = NULL_IVAL; //update to null value
  }
}

COMPUTED_COL_PREREQ* sales_qty_bucket_before(UTILITY *U) { //BUCK
  COMPUTED_COL_PREREQ *ret = sb_computed_col_prereq_factory(); //BUCK
  ret->destination_col_type_name = sb_clone("t_bucket" ); //BUCK
  COMPUTED_COL_NEED *needed_col = malloc(sizeof(COMPUTED_COL_NEED)); //BUCK
  ret->needed_col = needed_col; //BUCK
  needed_col->needed_col_count = 1; //BUCK
  needed_col->needed_col_names = malloc(needed_col->needed_col_count * sizeof(char*)); //BUCK
  needed_col->needed_col_names[0] = sb_clone("sales_qty" ); //BUCK
// ret->select_context_type = 'A'; //BUCK
  return ret; //BUCK
} //BUCK

void* sales_qty_bucket_before_select_typeA(UTILITY *U, char **params, int params_len, void *context) { //BUCK
  TYPE_A *select_context = malloc(sizeof(TYPE_A)); //BUCK
  select_context->len = U->fn_get_col_type_ival_count("sys#type#sales#sales_qty" ); //BUCK
  select_context->ival_needed_2_ival_dest = malloc(select_context->len * sizeof(U_INT)); //BUCK
  for (int i = 0; i < select_context-> len; i++) { //BUCK
    select_context->ival_needed_2_ival_dest[i] = 10; //BUCK
  } //BUCK
  select_context->ival_needed_2_ival_dest[11] = 11; //BUCK
  select_context->ival_needed_2_ival_dest[12] = 11; //BUCK
  return select_context; //BUCK
} //BUCK

void sales_qty_bucket(U_INT **ivals, float **num_vals, int ivals_len, void *context) { //BUCK
  float f = *num_vals[0];
  U_INT *ival = ivals[0];
  if (f <= 10) {
    *ival = 10;
  } else {
    *ival = 11;
  }
} //BUCK

set SO_FILE_NAME='Z_doc_comp_col_two_values_better.so';
reponse
success

refresh dirty view;
reponse
success

--#SB log_verbose
select line_id, new_customer_id from v_sales
;
line_idnew_customer_id
ID01#
ID02#
ID03#
ID04#
ID05#
ID06#
ID07#

select line_id from v_sales
and new_customer_id='C1'
;
line_id

select line_id from v_sales
and new_customer_id='C2'
;
line_id

select line_id, maxstr(new_customer_id) from v_sales group by line_id
;
line_idmaxstr(new_customer_id)

select item_id, list(new_customer_id) from v_sales group by item_id
;
item_idlist(new_customer_id)
artA
artB
#

select item_id, list(new_customer_id), list(customer_id) from v_sales group by item_id
;
item_idlist(new_customer_id)list(customer_id)
artAC1,C2
artBC1,C2
#C1

SB predefined contexts C API

A context is a C custom objects stored in memory be SB ant that can be used by other part of your custom C code
Some predefined contexts for most common operations are provided (see common.h)

./_SO_CODE/common.h


typedef struct idx1 {
  U_INT len;
  U_INT *ival1_to_ival2;
  char *origin_col_type_name; //SB_LIB_VERSION 12
  char *origin_col_name; //SB_LIB_VERSION 12
  char *destination_col_type_name;
  COMPUTED_COL_NEED *needed_col;
} IDX1;

typedef struct idx1_num {
  U_INT len;
  float *ival1_to_num;
  char *origin_col_type_name;
  char *origin_col_name;
  char *destination_col_type_name;
  char ratio_yn;
} IDX1_NUM;

typedef struct idx2 {
  U_INT **ival1_to_ival2_to_ival3;
  U_INT ival1_len;
  char *destination_col_type_name;
  COMPUTED_COL_NEED *needed_col;
} IDX2;

#define YYYYMMDD unsigned int

typedef struct line_period {
  U_INT ival;
  YYYYMMDD d1;
  YYYYMMDD d2;
} SB_PERIOD;

typedef struct line_periods {
  SB_PERIOD *lines;
  int count;
} SB_PERIODS;

typedef struct idx2_period {
  U_INT ival1_len;
  U_INT ival2_len;
  SB_PERIODS ***ival1_to_ival2_to_ival3_periods;
  char *destination_col_type_name;
  COMPUTED_COL_NEED *needed_col;
  YYYYMMDD *ival_to_d;
  int ival_to_d_len;
  char *d_col_type_name;
} IDX2_PERIOD;

COMPUTED_COL_PREREQ* sb_idx1_before(UTILITY *U, char *col1, char *col2);
COMPUTED_COL_PREREQ* sb_idx2_before(UTILITY *U, char *col1, char *col2, char *col3);
COMPUTED_COL_PREREQ* sb_idx2_period_before(UTILITY *U, char *col1, char *col2, char *col3, char *col4);
void sb_idx1(U_INT **ivals, float **num_vals, int ivals_len, void *context);
void sb_idx2(U_INT **ivals, float **num_vals, int ivals_len, void *context);
void sb_idx2_period(U_INT **ivals, float **num_vals, int ivals_len, void *context);
//

using context basics

Sometimes your computed column will need other information because the needed columns in not enough
Context should be used in that case
set COMPUTED_COLUMNS='last_fidelity_card,last_customer_info,valid_customer_info,best_customer_info';
reponse
success

select item_id, customer_id, sales_date, line_id from v_sales callback sort(4,'asc');
item_idcustomer_idsales_dateline_id
artAC120191231ID01
artBC220200102ID02
artBC120191231ID03
artBC220200102ID04
#C120200102ID05
artAC120191231ID06
artAC220191231ID07

select * from v_fidelity_cards;
customer_idcard_labelvalid_fromvalid_until
C1SILVER2019120120191231
C1GOLD2020120120201231

select * from v_item_customer_infos;
customer_iditem_idinfovalid_fromvalid_until
C1artAFREQUENT BUYER of artA in 20192019010120191231
C1artBFREQUENT BUYER of artB in 20202020010120201231
C2artBFREQUENT BUYER of artB in 20202020010120201231

--1 column join with fn_build_idx1
--last fidelity card per customer_id

./_SO_CODE/Z_doc_comp_col2_last_fidelity_card.c

#include "./common.h"

COMPUTED_COL_PREREQ* last_fidelity_card_before(UTILITY *U) {
  return sb_idx1_before(U, "customer_id" , "card_label" );
}

void last_fidelity_card(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  sb_idx1(ivals, num_vals, ivals_len, context);
}

void fn_free(void *c){
  free(c);
}


set SO_FILE_NAME='Z_doc_comp_col2_last_fidelity_card.so';
reponse
success

loop fidelity_cards(customer_id,card_label) function fn_build_idx1;
LOOP_RESULT
DONE

desc context;
context_name
get_deptA
mult_by_sales_priceB
divide_by_sales_priceB
IDX1#customer_id->card_label

refresh computed_column;
reponse
success

desc computed_column;
comp_col_nameerror
last_fidelity_cardno error
last_customer_infofunction not found: last_customer_info
valid_customer_infofunction not found: valid_customer_info
best_customer_infofunction not found: best_customer_info
get_deptno error
mult_by_sales_priceno error
divide_by_sales_priceno error

select item_id, customer_id, sales_date, line_id, last_fidelity_card from v_sales callback sort(4,'asc');
item_idcustomer_idsales_dateline_idlast_fidelity_card
artAC120191231ID01GOLD
artBC220200102ID02#
artBC120191231ID03GOLD
artBC220200102ID04#
#C120200102ID05GOLD
artAC120191231ID06GOLD
artAC220191231ID07#

select customer_id, list(last_fidelity_card) from v_sales group by customer_id callback sort(1,'asc');
customer_idlist(last_fidelity_card)
C1GOLD
C2

select sales.* from v_sales where last_fidelity_card like 'GO';
item_idcustomer_idsales_datesales_qtyline_idpackaging_id
artAC1201912315ID01box1
artBC1201912314ID03#
#C1202001028ID05#
artAC1201912315ID06box1

select count(*), list(customer_id) from v_sales and last_fidelity_card='GOLD';
count(*)list(customer_id)
4C1

--2 columns join with fn_build_idx2
--last customer_info per item_id/customer_id

./_SO_CODE/Z_doc_comp_col2_last_customer_info.c

#include "./common.h"

COMPUTED_COL_PREREQ* last_customer_info_before(UTILITY *U) {
  return sb_idx2_before(U, "customer_id" , "item_id" , "info" );
}

void last_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  sb_idx2(ivals, num_vals, ivals_len, context);
}

void fn_free(void *c){
  free(c);
}


set SO_FILE_NAME='Z_doc_comp_col2_last_customer_info.so';
reponse
success

loop item_customer_infos(customer_id,item_id,info) function fn_build_idx2;
LOOP_RESULT
DONE

desc context;
context_name
get_deptA
mult_by_sales_priceB
divide_by_sales_priceB
IDX1#customer_id->card_label
IDX2#customer_id->item_id->info

refresh computed_column;
reponse
success

desc computed_column;
comp_col_nameerror
last_fidelity_cardfunction not found: last_fidelity_card
last_customer_infono error
valid_customer_infofunction not found: valid_customer_info
best_customer_infofunction not found: best_customer_info
get_deptno error
mult_by_sales_priceno error
divide_by_sales_priceno error

select item_id, customer_id, sales_date, line_id, last_customer_info from v_sales callback sort(4,'asc');
item_idcustomer_idsales_dateline_idlast_customer_info
artAC120191231ID01FREQUENT BUYER of artA in 2019
artBC220200102ID02FREQUENT BUYER of artB in 2020
artBC120191231ID03FREQUENT BUYER of artB in 2020
artBC220200102ID04FREQUENT BUYER of artB in 2020
#C120200102ID05#
artAC120191231ID06FREQUENT BUYER of artA in 2019
artAC220191231ID07#

--2 columns join + period check with fn_build_idx2_period
--valid customer_info per item_id/customer_id, valid at the sales_date

./_SO_CODE/Z_doc_comp_col2_valid_customer_info.c

#include "./common.h"

COMPUTED_COL_PREREQ* valid_customer_info_before(UTILITY *U) {
  return sb_idx2_period_before(U, "customer_id" , "item_id" , "sales_date" , "info" );
}

void valid_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  sb_idx2_period(ivals, num_vals, ivals_len, context);
}

void fn_free(void *c){
  free(c);
}


set SO_FILE_NAME='Z_doc_comp_col2_valid_customer_info.so';
reponse
success

loop item_customer_infos(customer_id,item_id,info,valid_from,valid_until) function fn_build_idx2_period('sales_date');
LOOP_RESULT
DONE

desc context;
context_name
get_deptA
mult_by_sales_priceB
divide_by_sales_priceB
IDX1#customer_id->card_label
IDX2#customer_id->item_id->info
IDX2_PERIOD#customer_id->item_id->sales_date->info

refresh computed_column;
reponse
success

desc computed_column;
comp_col_nameerror
last_fidelity_cardfunction not found: last_fidelity_card
last_customer_infofunction not found: last_customer_info
valid_customer_infono error
best_customer_infofunction not found: best_customer_info
get_deptno error
mult_by_sales_priceno error
divide_by_sales_priceno error

select item_id, customer_id, sales_date, line_id, valid_customer_info from v_sales callback sort(4,'asc');
item_idcustomer_idsales_dateline_idvalid_customer_info
artAC120191231ID01FREQUENT BUYER of artA in 2019
artBC220200102ID02FREQUENT BUYER of artB in 2020
artBC120191231ID03#
artBC220200102ID04FREQUENT BUYER of artB in 2020
#C120200102ID05#
artAC120191231ID06FREQUENT BUYER of artA in 2019
artAC220191231ID07#

using custom context

Sometimes predefined context is not enough, or you needed several custom contexts
In such cas you should define your own context
--best_customer_info valid_customer_info or last_customer_info or last_fidelity_card

./_SO_CODE/Z_doc_comp_col2_best_customer_info.c

#include "./common.h"

typedef struct my_context {
  IDX1 *idx1;
  IDX2 *idx2;
  IDX2_PERIOD *idx2_p;
} MY_CONTEXT;

void fn_free(void *c){
  free(c);
}

int fn_build_my_context_before(SB_VALS* read, SB_VALS* new) {
  MY_CONTEXT *c = malloc(sizeof(MY_CONTEXT));
  c->idx1 = read->U->fn_get_context("IDX1#customer_id->card_label" );
  c->idx2 = read->U->fn_get_context("IDX2#customer_id->item_id->info" );
  c->idx2_p = read->U->fn_get_context("IDX2_PERIOD#customer_id->item_id->sales_date->info" );
  if (c->idx1 == NULL) {
    read->U->fn_log("fn_build_my_context_before: context IDX1#customer_id->card_label not found" );
    return KO;
  }
  if (c->idx2 == NULL) {
    read->U->fn_log("fn_build_my_context_before: context IDX2#customer_id->item_id->info not found" );
    return KO;
  }
  if (c->idx2_p == NULL) {
    read->U->fn_log(
      "fn_build_my_context_before: context IDX2_PERIOD#customer_id->item_id->sales_date->info not found" );
    return KO;
  }
  read->context = c;
  read->context_name = sb_clone("my_context" );
  read->fn_free_context_name = sb_clone("fn_free" );
  return OK;
}

int fn_build_my_context(SB_VALS* read, SB_VALS* new) {
  return NOT_MODIFIED;
}

COMPUTED_COL_PREREQ* best_customer_info_before(UTILITY *U) {
  COMPUTED_COL_PREREQ*ret = sb_computed_col_prereq_factory();
  ret->needed_context_name = sb_clone("my_context" );
  MY_CONTEXT* idx = U->fn_get_context(ret->needed_context_name);
  if (idx == NULL) {
    ret->error = sb_concat3("context " , ret->needed_context_name, " not found" );
    return ret;
  }
  ret->destination_col_type_name = sb_clone(idx->idx2_p->destination_col_type_name);
  ret->needed_col = sb_clone_need_col(idx->idx2_p->needed_col);
  return ret;
}

void best_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  MY_CONTEXT *c = context;
  U_INT customer_id = *ivals[0];
  sb_idx2_period(ivals, num_vals, ivals_len, c->idx2_p);
  if (*ivals[0] == NULL_IVAL) {
    *ivals[0] = customer_id;
    sb_idx2(ivals, num_vals, ivals_len, c->idx2);
    if (*ivals[0] == NULL_IVAL) {
      *ivals[0] = customer_id;
      sb_idx1(ivals, num_vals, ivals_len, c->idx1);
    }
  }
}


set SO_FILE_NAME='Z_doc_comp_col2_best_customer_info.so';
reponse
success

--create an empty table to trigger fn_build_my_context
create table empty_table (txt text);
reponse
success

loop empty_table(txt) function fn_build_my_context;
LOOP_RESULT
DONE

desc context;
context_name
get_deptA
mult_by_sales_priceB
divide_by_sales_priceB
IDX1#customer_id->card_label
IDX2#customer_id->item_id->info
IDX2_PERIOD#customer_id->item_id->sales_date->info
my_context

refresh computed_column;
reponse
success

desc computed_column;
comp_col_nameerror
last_fidelity_cardfunction not found: last_fidelity_card
last_customer_infofunction not found: last_customer_info
valid_customer_infofunction not found: valid_customer_info
best_customer_infono error
get_deptno error
mult_by_sales_priceno error
divide_by_sales_priceno error

select item_id, customer_id, sales_date, line_id, best_customer_info from v_sales callback sort(4,'asc');
item_idcustomer_idsales_dateline_idbest_customer_info
artAC120191231ID01FREQUENT BUYER of artA in 2019
artBC220200102ID02FREQUENT BUYER of artB in 2020
artBC120191231ID03FREQUENT BUYER of artB in 2020
artBC220200102ID04FREQUENT BUYER of artB in 2020
#C120200102ID05GOLD
artAC120191231ID06FREQUENT BUYER of artA in 2019
artAC220191231ID07#

--all computed columns

./_SO_CODE/Z_doc_comp_col2_all.c

#include "./common.h"

COMPUTED_COL_PREREQ* last_fidelity_card_before(UTILITY *U) {
  return sb_idx1_before(U, "customer_id" , "card_label" );
}

void last_fidelity_card(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  sb_idx1(ivals, num_vals, ivals_len, context);
}

COMPUTED_COL_PREREQ* last_customer_info_before(UTILITY *U) {
  return sb_idx2_before(U, "customer_id" , "item_id" , "info" );
}

void last_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  sb_idx2(ivals, num_vals, ivals_len, context);
}

COMPUTED_COL_PREREQ* valid_customer_info_before(UTILITY *U) {
  return sb_idx2_period_before(U, "customer_id" , "item_id" , "sales_date" , "info" );
}

void valid_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  sb_idx2_period(ivals, num_vals, ivals_len, context);
}

typedef struct my_context {
  IDX1 *idx1;
  IDX2 *idx2;
  IDX2_PERIOD *idx2_p;
} MY_CONTEXT;

void fn_free(void *c){
  free(c);
}

int fn_build_my_context_before(SB_VALS* read, SB_VALS* new) {
  MY_CONTEXT *c = malloc(sizeof(MY_CONTEXT));
  c->idx1 = read->U->fn_get_context("IDX1#customer_id->card_label" );
  c->idx2 = read->U->fn_get_context("IDX2#customer_id->item_id->info" );
  c->idx2_p = read->U->fn_get_context("IDX2_PERIOD#customer_id->item_id->sales_date->info" );
  if (c->idx1 == NULL) {
    read->U->fn_log("fn_build_my_context_before: context IDX1#customer_id->card_label not found" );
    return KO;
  }
  if (c->idx2 == NULL) {
    read->U->fn_log("fn_build_my_context_before: context IDX2#customer_id->item_id->info not found" );
    return KO;
  }
  if (c->idx2_p == NULL) {
    read->U->fn_log(
      "fn_build_my_context_before: context IDX2_PERIOD#customer_id->item_id->sales_date->info not found" );
    return KO;
  }
  read->context = c;
  read->context_name = sb_clone("my_context" );
  read->fn_free_context_name = sb_clone("fn_free" );
  return OK;
}

int fn_build_my_context(SB_VALS* read, SB_VALS* new) {
  return NOT_MODIFIED;
}

COMPUTED_COL_PREREQ* best_customer_info_before(UTILITY *U) {
  COMPUTED_COL_PREREQ*ret = sb_computed_col_prereq_factory();
  ret->needed_context_name = sb_clone("my_context" );
  MY_CONTEXT* idx = U->fn_get_context(ret->needed_context_name);
  if (idx == NULL) {
    ret->error = sb_concat3("context " , ret->needed_context_name, " not found" );
    return ret;
  }
  ret->destination_col_type_name = sb_clone(idx->idx2_p->destination_col_type_name);
  ret->needed_col = sb_clone_need_col(idx->idx2_p->needed_col);
  return ret;
}

void best_customer_info(U_INT**ivals, float**num_vals, int ivals_len, void *context) {
  MY_CONTEXT *c = context;
  U_INT customer_id = *ivals[0];
  sb_idx2_period(ivals, num_vals, ivals_len, c->idx2_p);
  if (*ivals[0] == NULL_IVAL) {
    *ivals[0] = customer_id;
    sb_idx2(ivals, num_vals, ivals_len, c->idx2);
    if (*ivals[0] == NULL_IVAL) {
      *ivals[0] = customer_id;
      sb_idx1(ivals, num_vals, ivals_len, c->idx1);
    }
  }
}


set SO_FILE_NAME='Z_doc_comp_col2_all.so';
reponse
success

refresh computed_column;
reponse
success

desc computed_column;
comp_col_nameerror
last_fidelity_cardno error
last_customer_infono error
valid_customer_infono error
best_customer_infono error
get_deptno error
mult_by_sales_priceno error
divide_by_sales_priceno error

select item_id, customer_id, sales_date, line_id, last_fidelity_card, last_customer_info, valid_customer_info, best_customer_info from v_sales callback sort(4,'asc');
item_idcustomer_idsales_dateline_idlast_fidelity_cardlast_customer_infovalid_customer_infobest_customer_info
artAC120191231ID01GOLDFREQUENT BUYER of artA in 2019FREQUENT BUYER of artA in 2019FREQUENT BUYER of artA in 2019
artBC220200102ID02#FREQUENT BUYER of artB in 2020FREQUENT BUYER of artB in 2020FREQUENT BUYER of artB in 2020
artBC120191231ID03GOLDFREQUENT BUYER of artB in 2020#FREQUENT BUYER of artB in 2020
artBC220200102ID04#FREQUENT BUYER of artB in 2020FREQUENT BUYER of artB in 2020FREQUENT BUYER of artB in 2020
#C120200102ID05GOLD##GOLD
artAC120191231ID06GOLDFREQUENT BUYER of artA in 2019FREQUENT BUYER of artA in 2019FREQUENT BUYER of artA in 2019
artAC220191231ID07####

using indexes on computed columns

Index can be used if there is only one needed column and that this column has an index (is part of a dimension)
--index on last_fidelity_card can be used but not on last_customer_info
--#SB use_index
select line_id from v_sales
and last_fidelity_card='GOLD'
and last_customer_info='FREQUENT BUYER of artA in 2019'
;
line_id
ID01
ID06

-- refresh computed_column;
-- refresh cache;
-- ###########################
-- RUNNING shutdown.sql
shutdown;