SAP HANA SQLScript 中的CE Plan Operators 包括两种:Data Source Acess和Relational Operators。其中Data Source Acess包括CE_COLUMN_TABLE、CE_JOIN_VIEW、CE_OLAP_VIEW、CE_CALC_VIEW; Relational Operators包括CE_JOIN、CE_LEFT_OUTER_JOIN、CE_RIGHT_OUTER_JOIN、CE_PROJECTION、CE_CALC、CE_AGGREGATION、CE_UNION_ALL、CE_VERTICAL_UNION、CE_CONVERSEION。下面主要就几个重要的介绍。
CE_PROJECTION
语法:
- CE_PROJECTION (:var_table, [param_name [AS new_param_name], ...],[Filter])
CE_PROJECTION (:var_table, [param_name [AS new_param_name], ...],[Filter])
例子::
- ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE","CRCY" AS "CURRENCY"], '"PRICE" > 50');
ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE","CRCY" AS "CURRENCY"], '"PRICE" > 50');
相当于:
- ot_books2= SELECT title, price, crcy AS currency FROM :it_books WHERE price > 50;
ot_books2= SELECT title, price, crcy AS currency FROM :it_books WHERE price > 50;
解释:
用于限制变量表的列的输出,可以选择性的重命名,计算表达式或者包含过滤器。
CE_CALC
语法:
- CE_CALC('expr', [Result Type])
CE_CALC('expr', [Result Type])
例子:
- with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",
with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",
- <SPAN style="WHITE-SPACE: pre"> </SPAN>CE_CALC('"SALES" * :vat_rate',decimal(10,2)) AS "SALES_VAT"],
- <SPAN style="WHITE-SPACE: pre"> </SPAN>'"CNAME" = '':cname''');
CE_CALC('"SALES" * :vat_rate',decimal(10,2)) AS "SALES_VAT"], '"CNAME" = '':cname''');
相当于:
- with_tax2 = select CID, CNAME, OID, SALES, (SALES * :vat_rate) as SALES_VAT from :product where CNAME = ':name';
with_tax2 = select CID, CNAME, OID, SALES, (SALES * :vat_rate) as SALES_VAT from :product where CNAME = ':name';
解释:
主要用于其他运算中,请注意例子中的参数的表现形式。
CE_AGGREGATION
语法:
- CE_AGGREGATE(:var_table, [aggregate("column")[AS "renamed_col"]], ["column", ...])
CE_AGGREGATE(:var_table, [aggregate("column")[AS "renamed_col"]], ["column", ...])
例子:
- ot_books1 = CE_AGGREGATION (:it_books,[COUNT ("PUBLISHER") AS "CNT"], ["YEAR"]);
ot_books1 = CE_AGGREGATION (:it_books,[COUNT ("PUBLISHER") AS "CNT"], ["YEAR"]);
相当于:
- ot_books2 = SELECT COUNT (publisher) AS cnt, year FROM :it_books GROUP BY year;
ot_books2 = SELECT COUNT (publisher) AS cnt, year FROM :it_books GROUP BY year;
解释:
用于聚合运算。