sap hana ce functions

7/26/2019 Sap Hana CE Functions

1/7

SAP HANA CE FunctionsSAP HANA CE Functions - Calculation Engine Plan Operators

Calculation engine plan operators encapsulate data-transformationfunctionality and can be used in the denition of functions. They constitutean alternative to using SQL statements as their logic is directly implementedin the calculation engine, i.e. the execution environment of SQLScript.

There are dierent categories of operators. !ata Source "ccess operators that bind a column table or a column vie# to

a table variable.

$elational operators that allo# a user to bypass the SQL processor duringevaluation and to directly interact #ith the calculation engine.

Special extensions implement, e.g., crucial business functions inside thedatabase %ernel.

Data Source Access Operators

1. CE_COLUMN_TABLEThe C&'C(L)*+'T"L& operator provides access to an existing columntable.

&xampleot'boo%s / C&'C(L)*+'T"L&01((2S134ot'boo%s5 / C&'C(L)*+'T"L&01((2S1, 61T7TL&1,18$7C&1,1C$C91:34

This example only #or%s on a column table and does not invo%e the SQLprocessor.

7t is semantically e;uivalent to the follo#ing

ot'boo%s< / S&L&CT = >$(* boo%s4ot'boo%s? / S&L&CT title, price, crcy >$(* boo%s4

2. CE_O!N_"!E#

The C&'@(7+'A7&B operator returns results for an existing oin vie# 0also%no#n as "ttribute Aie#3.

out / C&'@(7+'A7&B018$(!)CT'S"L&S1, 618$(!)CT'2&91,18$(!)CT'T&DT1, 1S"L&S1:34

$etrieves the attributes 8$(!)CT'2&9, 8$(!)CT'T&DT, and S"L&S from theoin vie# 8$(!)CT'S"L&S.

7t is e;uivalent to the follo#ing SQL

out / S&L&CT product'%ey, product'text, sales >$(* product'sales4
http://inmemoryhana.blogspot.com/2012/11/sap-hana-ce-functions.htmlhttp://inmemoryhana.blogspot.com/2012/11/sap-hana-ce-functions.html


2/7

3. CE_OLAP_"!E#

The C&'(L"8'A7&B operator returns results for an existing (L"8 vie# 0also%no# as an "nalytical Aie#3.

out / C&'(L"8'A7&B01(L"8'vie#1, 61!7*1, 12>1:34

7s e;uivalent to the follo#ing SQL

out / select dim, S)*0%f3 >$(* (L"8'vie# E$()8 9 dim4

4. CE_CALC_"!E#

The C&'C"LC'A7&B operator returns results for an existing calculation vie#.

&xampleout / C&'C"LC'A7&B01'S9S'SS'C&'T&STC&CT"L&'$&T1, 61C7!1, 1C+"*&1:34

Semantically e;uivalent to the follo#ing SQL

out / S&L&CT cid, cname >$(* 1'S9S'SS'C&'T&STC&CT"L&'$&T14

$elational operators

The calculation engine plan operators presented in this section provide thefunctionality of relational operators #hich are directly executed in the

calculation engine.

5. CE_O!NThe C&'@(7+ operator calculates a natural 0inner3 oin of the given pair oftables on a list of oin attributes. >or each pair of oin attributes, only oneattribute #ill be in the result. (ptionally, a proection list of attribute namescan be given to restrict the output to the given attributes. 7f a proection listis provided, it must include the oin attributes. >inally, the plan operatorre;uires each pair of oin attributes to have identical attribute names. 7ncase of oin attributes having dierent names, one of them must berenamed prior to the oin.

E%a&pleot'pubs'boo%s / C&'@(7+0lt'pubs, it'boo%s,618)L7SF&$1:34ot'pubs'boo%s5 / C&'@(7+0lt'pubs, it'boo%s,618)L7SF&$1:, 61T7TL&1,1+"*&1,18)L7SF&$1 ,19&"$1 :34

This example is semantically e;uivalent to the follo#ing SQL but does notinvo%e the SQL processor.

ot'pubs'boo%s< / S&L&CT8.publisher "Spublisher, name, street, post'code,city, country, isbn, title, edition, year, price,crcy >$(*lt'pubs "S8,

it'boo%s "SBF&$&8.publisher / .publisher4


3/7

ot'pubs'boo%s? / S&L&CTtitle, name, 8.publisher "S publisher,year >$(*lt'pubs "S 8, it'boo%s "S BF&$&8.publisher / .publisher4

6. CE_LEFT_OUTE$_O!N

Calculate the left outer oin. esides the function name, the syntax is thesame as for C&'@(7+.

7. CE_$!'HT_OUTE$_O!N

Calculate the right outer oin. esides the function name, the syntax is thesame as for C&'@(7+.

8. CE_FULL_OUTE$_O!Nis not supported.

9.

CE_P$OECT!ON

$estricts the columns in the schema of table variable var'table to thosementioned in the proection list.

. " variable of type table #hich is subect to the proection. Li%e C&'@(7+,C&'8$(@&CT7(+ cannot handle tables directly as input.

5. " list of attributes #hich should be in the resulting table. The list must atleast have one element. The attributes can be renamed using the SQL%ey#ord "S, and expressions can be evaluated using the C&'C"LC function.


4/7

#ith'tax5 / S&L&CT cid, cname, oid, sales, sales = vat'rate as sales'vat>$(* product BF&$& cname / GcnameG4

"nother fre;uent use case of C&'C"LC is computing ro# numbers

C$&"T& 8$(C&!)$& ceEet$o#+um07+ it'boo%s boo%s,()T ran%ed'boo%s ot'ran%ed'boo%s3L"+E)"E& SQLSC$78T $&"!S SQL !"T" "S&E7+ordered'boo%s / S&L&CT title, price, crcy>$(* it'boo%s ($!&$ 9 price !&SC4ran%ed'boo%s / C&'8$(@&CT7(+0it'boo%s, 61T7TL&1, 18$7C&1,C&'C"LC0Gro#num03G, integer3 "S 1$"+21,1C$C91 "S 1C)$$&+C91:34&+!4

11. CE_A''$E'AT!ONEroups the input and computes aggregates for each group.

&xampleot'boo%s / C&'"EE$&E"T7(+ 0it'boo%s, 6C()+T 018)L7SF&$13 "S1C+T1:, 619&"$1:34Semantically e;uivalent to the follo#ing SQLot'boo%s5 / S&L&CT C()+T 0publisher3 "S cnt, year >$(* it'boo%s E$()89 year4

12.CE_UN!ON_ALLThe C&')+7(+'"LL function is semantically e;uivalent to SQL )+7(+ "LL

statement. 7t computes the union of t#o tables #hich need to have identicalschemas. The C&')+7(+'"LL function preserves duplicates

&xampleot'all'boo%s / C&')+7(+'"LL 0lt'boo%s, it'audioboo%s34Semantically e;uivalent to the follo#ing SQLot'all'boo%s5 / S&L&CT = >$(* lt'boo%s )+7(+ "LL S&L&CT = >$(*it'audioboo%s4

Special (perators

7n this section #e discuss operators that have no immediate counterpart inSQL.

13. CE_"E$T!CAL_UN!ON

>or each input, applies the concatenation of their columns. (ptionallycolumns can be renamed. Clearly, all input tables must have the samecardinality.SyntaxC&'A&$T7C"L')+7(+0input, 6proect'att K"S ne#'param'name, ...:,input5, 6proect'att K"S ne#'param'name, ...:, ...3


5/7

&xampleout / C&'A&$T7C"L')+7(+0rstname, 617!1, 1>7$ST+"*&1 "S1E7A&++"*&1:, lastname, 61L"ST+"*&1 "S 1>"*7L9+"*&1:34

The vertical union is sensitive to the ordering of its input. SQL statements

and many calculation engine plan operators may reorder their input orreturn their result in dierent orders across invocations. This may lead tounexpected results.

14. CE_CON"E$S!ON"pplies a unit conversion to input table GinputG and returns the convertedvalues. $esult columns can optionally be renamed.

2ey Aalues Type

*andatory

!efault !ocumentation

GfamilyG GcurrencyG %ey 9 none the family of theconversionto be used

GmethodG G&$8G %ey 9 none theconversionmethod

Merror'handlingN

Gfail on errorG,Gset to nullG,G%eepunconvertedG

%ey + Gfail on errorG The reactionif a ratecould not bedeterminedfor a ro#

GoutputG combinationsof GinputG,GunconvertedG,GconvertedG,Gpassed'throughG,Goutput'unitG,Gsource'unitG,

%ey + Gconverted,passed'through,output'unitG

#hichattributesshould beincluded inthe output

2ey Aalues

Type *andatory

!efault !ocumentation

Gtarget'unitG, Greference'dateGGsource'unitG "ny Consta

nt+ +one the default

source unitfor any %indofconversion

Gtarget'unitG "ny Constant

+ +one the defaulttarget unitfor any %indof

conversionGreference'dateG "ny Consta + +one the default


6/7

nt referencedate for any%ind ofconversion

Gsource'unit'columnG

column ininputtable

columnname

+ +one the name of the columncontainingthe sourceunit in theinput table

Gtarget'unit'columnG

column ininputtable

columnname

+ +one the name of the columncontainingthe targetunit in theinput table

Greference'date'col

umnG

colu

mn ininputtable

colum

nname

+ +one the default

referencedate for any%ind ofconversion

Goutput'unit'columnG

"ny columnname

+ 1()T8)T')+7T1

the name ofthe columncontainingthe targetunit in theoutput table

"nd for &$8-Conversion in particular also

2ey Aalues Type *andatory !efault !ocumentation

GclientG "ny Constant 9 +one the client asstored in thetables

Gconversion'typeG

"ny Constant + G*G theconversiontype asstored in thetables

GschemaG "ny schemaname

+ currentschema

the defaultschema in#hich theconversiontablesshould beloo%ed up


7/7

&xampleconv'tab / C&'C(+A&$S7(+0input, 6family / GcurrencyG, method / G&$8G,client / GJJ?G, conversion'type / G*G, target'unit / G&)$G,source'unit'column / 1B"&$21, reference'date'column / 1&$!"T1,output'unit'column / 1T$EC)$1:34

15. T$ACE

The T$"C& operator is used to debug SQLScript procedures. 7t traces thetabular data passed as its argument into a local temporary table and returnsits input unmodied. The names of the temporary tables can be retrievedfrom the S9S.SQLSC$78T'T$"C& vie#.

out / T$"C&0input34

sap hana ce functions

Documents