Introduction
SAP HANA SQLScript is an advanced scripting language that enhances the capabilities of SQL by introducing imperative logic, looping constructs, and various operators. This guide explores key SQLScript operators used in SAP HANA, their syntax, and how to apply them efficiently.1. CE_JOIN_VIEW Operator
The CE_JOIN_VIEW operator retrieves results from an existing join view (also known as an Attribute View). It takes the name of the join view and an optional list of attributes as parameters.Example:
out = CE_JOIN_VIEW(“PRODUCT_SALES”, [“PRODUCT_KEY”, “PRODUCT_TEXT”, “SALES”]);
2. CE_OLAP_VIEW Operator
The CE_OLAP_VIEW operator fetches results from an OLAP view (Analytical View). It takes the OLAP view name and an optional list of key figures and dimensions as parameters. The results are aggregated based on dimensions.Example:
out = CE_OLAP_VIEW (“OLAP_view”, [“Dim1”, “kf”]);Equivalent SQL:
SELECT Dim1, SUM(kf) FROM OLAP_view GROUP BY Dim1;
3. CE_CALC_VIEW Operator
The CE_CALC_VIEW operator retrieves data from an existing Calculation View. It takes the view name and an optional list of attributes.Example:
out = CE_CALC_VIEW (“CALC_view”, [“CID”, “CNAME”]);Equivalent SQL:
SELECT CID, CNAME FROM CALC_view;
4. CE_JOIN Operator
The CE_JOIN operator performs an inner join on two tables using a specified list of join attributes.Example:
ot_pubs_books1 = CE_JOIN(:lt_pubs, :it_books, [“PUBLISHER”]);Equivalent SQL:
SELECT P.publisher, name, title, year FROM :lt_pubs AS P, :it_books AS B WHERE P.publisher = B.publisher;
5. CE_LEFT_OUTER_JOIN Operator
The CE_LEFT_OUTER_JOIN operator performs a left outer join. The syntax is similar to CE_JOIN.6. CE_RIGHT_OUTER_JOIN Operator
The CE_RIGHT_OUTER_JOIN operator performs a right outer join. The syntax is similar to CE_JOIN.7. CE_PROJECTION Operator
The CE_PROJECTION operator restricts table columns, renames columns, applies filters, and computes expressions.Example:
ot_books1 = CE_PROJECTION(:it_books, [“TITLE”, “PRICE”, “CRCY” AS “CURRENCY”], '“PRICE” > 50');Equivalent SQL:
SELECT TITLE, PRICE, CRCY AS CURRENCY FROM :it_books WHERE PRICE > 50;
8. CE_CALC Operator
The CE_CALC operator evaluates expressions and binds them to new columns.Example:
with_tax = CE_PROJECTION(:product, [“CID”, “CNAME”, “OID”, “SALES”,
CE_CALC('“SALES” * :vat_rate', decimal(10,2)) AS “SALES_VAT”],
''“CNAME” = ':cname''');
Equivalent SQL:
SELECT CID, CNAME, OID, SALES, SALES * :vat_rate AS SALES_VAT FROM :product WHERE CNAME = ':cname';
9. CE_AGGREGATION Operator
The CE_AGGREGATION operator groups input data and computes aggregate functions such as SUM, COUNT, MAX, and MIN.Example:
ot_books1 = CE_AGGREGATION(:it_books, [COUNT (“PUBLISHER”) AS CNT], [“YEAR”]);Equivalent SQL:
SELECT COUNT(PUBLISHER) AS CNT, YEAR FROM :it_books GROUP BY YEAR;
10. CE_UNION_ALL Operator
The CE_UNION_ALL operator performs a union operation on two tables with identical schemas.Example:
ot_all_books1 = CE_UNION_ALL(:lt_books, :it_audit_books);Equivalent SQL:
SELECT * FROM :lt_books UNION ALL SELECT * FROM :it_audit_books;
11. Special Operators in SQLScript
SAP HANA provides special operators such as:- CE_VERTICAL_UNION
- CE_CONVERSION
12. Debugging SQLScript Procedures
SAP HANA provides the TRACE operator for debugging SQLScript procedures.Example:
out = TRACE(:input);This operator logs tabular data into a temporary table, which can be retrieved from the SYS.SQLSCRIPT_TRACE view.
13. Setting Up Tracing
To enable tracing:- Open the Administration Perspective.
- Navigate to the Trace Configuration tab.
- Ensure you have the TRACE ADMIN and INFILE ADMIN system privileges.
14. Difference Between BREAK and CONTINUE
- BREAK: Exits the loop immediately.
- CONTINUE: Skips the current iteration and moves to the next.
15. Using Cursors in SQLScript
Cursors allow fetching single rows from query result sets.Example:
CREATE PROCEDURE cursor_proc LANGUAGE SQLSCRIPT AS
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;
BEGIN
OPEN c_cursor1('978-3-86894-012-1');
FETCH c_cursor1 INTO v_isbn, v_title, v_price, v_crcy;
CLOSE c_cursor1;
END;
16. Looping Over Cursor Result Sets
SQLScript supports iterating over cursor result sets using a FOR loop.Example:
CREATE PROCEDURE foreach_proc() LANGUAGE SQLSCRIPT ASv_isbn VARCHAR(20) := '';
CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR
SELECT isbn, title, price, crcy FROM books ORDER BY isbn;
BEGIN
FOR cur_row AS c_cursor1 DO
CALL ins_msg_proc('Book title is: ' || cur_row.title);
END FOR;
END;
Frequently Asked Questions (FAQs)
1. What is the difference between CE_JOIN and CE_LEFT_OUTER_JOIN?
CE_JOIN performs an inner join, while CE_LEFT_OUTER_JOIN retains all records from the left table even if no match is found.2. How can I debug SQLScript procedures?
Use the TRACE operator and check the SYS.SQLSCRIPT_TRACE view.3. Can CE_CALC be used for row numbering?
Yes, CE_CALC('rownum()', integer) AS "RANK" can be used for row numbering.4. What system privileges are required for tracing?
You need TRACE ADMIN and INFILE ADMIN privileges.5. What is the difference between UNION and UNION ALL?
- UNION removes duplicates.
- UNION ALL retains duplicates.
6. What are the special operators in SQLScript?
Special operators include:- - CE_VERTICAL_UNION
- - CE_CONVERSION
Conclusion
SAP HANA SQLScript provides a powerful set of operators for data manipulation, aggregation, and debugging. Understanding these operators allows developers to write efficient and optimized SQLScript code.