SAP Hana sqlscript

The CE plan operators in SAP Hana sqlscript include data source acess and Relational operators. Data Source acess includes ce_column_table, operator, operator, ce_calc_view; Relational operators include ce_join, operator, operator, ce_projection, ce_calc, ce_aggregation, ce_union_all, operator, ce_converseion. Below are some important introductions.

Ce_projection

Syntax:

CE_PROJECTION (:var_table, [param_name [AS new_param_name], ...],[Filter])

Example ::

ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE","CRCY" AS "CURRENCY"], '"PRICE" > 50');

Equivalent:

ot_books2= SELECT title, price, crcy AS currency FROM :it_books WHERE price > 50;

Explanation:

It is used to restrict the output of columns in a variable table and can be selectively renamed, computed expressions, or contains filters.

Ce_calc

Syntax:

CE_CALC('expr', [Result Type])

Example:

with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",

CE_CALC('"SALES" * :vat_rate',decimal(10,2)) AS "SALES_VAT"],'"CNAME" = '':cname''');

Equivalent:

with_tax2 = select CID, CNAME, OID, SALES, (SALES * :vat_rate) as SALES_VAT from :product where CNAME = ':name';

Explanation:

It is mainly used in other operations. Pay attention to the Parameter Representation in the example.

Ce_aggregation

Syntax:

CE_AGGREGATE(:var_table, [aggregate("column")[AS "renamed_col"]], ["column", ...])

Example:

ot_books1 = CE_AGGREGATION (:it_books,[COUNT ("PUBLISHER") AS "CNT"], ["YEAR"]);

Equivalent:

ot_books2 = SELECT COUNT (publisher) AS cnt, year FROM :it_books GROUP BY year;

Explanation:

Used for aggregation operations.