SAP HANA Questions on SQLScript #2

17. What is CE_JOIN_VIEW operator?
It return results for an existing join view (also known as Attribute view). It takes the name of join view and optional list of attributes as parameters.
18. What is  CE_OLAP_VIEW operator?
It return results for an existing OLAP view (also known as Analytical view). It takes the name of OLAP view and an optional list of key figures and dimensions as parameters. The OLAP view is grouped by dimensions and the key figures are aggregated using the default aggregation of the OLAP view.
out = CE_OLAP_VIEW (“OLAP_view”,[“Dim1”, “kf”]);
Is equivalent to
out = SELECT dim1, SUM(kf) FROM OLAP_view GROUP BY dim1;
19. What is CE_CALC_VIEW operator?
It returns results for an existing calculation view. It takes the name of the calculation view and optionally a projection list of attribute names.
out = CE_CALC_VIEW (“CALC_view”, [“CID”, “CNAME”]);
Is equivalent to
out = SELECT  cid, cname FROM CALC_view;
20. What is CE_JOIN operator?
It calculates a inner join of the given pair of tables on a list of join attributes. Each pair of join attributes must have identical attribute names  else one of them  must be renamed prior to the join.
ot_pubs_books1 = CE_JOIN (:lt_pubs, :it_books, [“PUBLISHER”]);
ot_pubs_books2 = CE_JOIN (:lt_pubs, :it_books, [“PUBLISHER”], [“TITLE”, “NAME”, “PUBLISHER”, “YEAR”]);
Is equivalent to
ot_pubs_books3 = SELECT P.publisher as publisher, name, street, post_code, city,              
                                                country, isbn, title, edition, year, price, crcy
                                   FROM :lt_pubs AS P, :it_books as B
                                   WHERE P.publisher = B.publisher;
ot_pubs_books4 = SELECT  title, name, P.publisher as publisher, year
                                   FROM :lt_pubs AS P, :it_books AS B
                                   WHERE P.publisher = B.publisher;
21. What is CE_LEFT_OUTER_JOIN operator?
It calculates the left outer join. Besides the function name the syntax is same as  CE_JOIN.
22. What is CE_RIGHT_OUTER_JOIN operator?
It calculates the right outer join. Besides the function name the syntax is same as  CE_JOIN.
23. What is CE_PROJECTION operator?
It restricts the columns in the table variable and optionally renames columns, computes expression, or applies a filter.
ot_books1 = CE_PROJECTION(:it_books, [“TITLE”, “PRICE”, “CRCY” AS “CURRENCY”], '“PRICE” > 50');
Is equivalent to
ot_books2 =  SELECT  title, price, crcy AS currency
                         FROM :it_books WHERE price > 50;
24. What is CE_CALC operator?
It evaluates an expression and then bound to a new column
with_tax = CE_PROJECTION(:product, [“CID”, “CNAME”, “OID”, “SALES”,
                                                                     CE_CALC('”SALES” * :vat_rate', decimal(10,2)) AS “SALES_VAT”],
                                                                      ''”CNAME” = ':cname''');
Is equivalent to
with_tax2 = SELECT cid, cname, oid, sales, sales * :vat_rate AS sales_vat
                       FROM :product
                       WHERE cname = ':cname';
All columns used in CE_CALC have to be included in the projection list. Another frequent use of CE_CALC is computing row numbers.
CE_CALC('rownum()', integer) AS "RANK"
25.What is CE_AGGREGATION operator?
It groups the input and computes aggregates for each group.
Supported aggregate functions are  count(“column”), sum(“column”), max(“column”), min(“column”).
Use sum(“column”)/count(“column”) to compute the average.
ot_books1 = CE_AGGREGATION(:it_books, [COUNT (“PUBLISHER”) AS CNT], [“YEAR”]);
Is equivalent to
ot_books2 = SELECT COUNT (publisher) as cnt, year FROM :it_books GROUP BY year;
26. What is CE_UNION_ALL operator?
It computes the union of two tables which need to have identical schemas.
ot_all_books1 = CE_UNION_ALL (:lt_books, :it_audit_books);
Is equivalent to
ot_all_books2 = SELECT * FROM :lt_books
                              UNION ALL
                              SELECT * FROM :it_audit_books;
27. What is special operator?
CE_VERTICAL_UNION and CE_CONVERSION are the special operators which do not have any immediate SQL equivalent.
28. What operator is used to debug SQLScript procedures?
TRACE operaor. It traces the tabular data passed as its argument into a local temporary table and return its input unmodified. The names of the temporary tables can be retrieved from the SYS.SQLSCRIPT_TRACE view.
Example: out = TRACE (:input);
29. How to set-up tracing?
From the Administration perspective, navigate to tab “Trace Configuration” . In order to change settings, you need to have system privileges “TRACE ADMIN” and “INFILE ADMIN”.
30. What is the difference between BREAK and CONTINUE?
Break means loop should stop processing, CONTINUE means loop should stop processing the current iteration and immediately start processing the next iteration.
31. What is Cursor?
It is used to fetch single rows from the result set returned by a query.
v_isbn VARCHAR(20);
CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR
SELECT isbn, title, price, crcy FROM books WHERE isbn = :v_isbn
ORDER BY isbn;
OPEN c_cursor1('978-3-86894-012-1');
IF c_cursor1::ISCLOSED THEN CALL ins_msg_proc('WRONG: cursor not open');
ELSE CALL ins_msg_proc('OK: cursor open');
FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;
IF c_cursor1::NOTFOUND THEN CALL ins_msg_proc('WRONG: cursor contains no valid data');
ELSE CALL ins_msg_proc('OK: cursor contains valid data');
CLOSE c_cursor1;
32. How to loop cursor over result sets?
Using FOR loop.
v_isbn VARCHAR(20) := '';
CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR
SELECT isbn, title, price, crcy FROM books ORDER BY isbn;
FOR cur_row as c_cursor1 DO
  CALL ins_msg_proc('book title is: ' || cur_row.title);


SAP HANA Hints and Tips

Main Index
SAP ERP Modules, Basis, ABAP and Other IMG Stuff

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.  The site is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.