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.
Example:
out = CE_JOIN_VIEW (“PRODUCT_SALES”, [“PRODUCT_KEY”,
“PRODUCT_TEXT”, “SALES”]);
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.
Example:
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.
Example:
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.
Example:
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.
Example:
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
Example:
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.
Example:
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.
Example:
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.
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');
IF c_cursor1::ISCLOSED THEN CALL ins_msg_proc('WRONG:
cursor not open');
ELSE CALL ins_msg_proc('OK: cursor open');
END IF;
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');
END IF;
CLOSE c_cursor1;
END;
32. How to loop cursor over result sets?
Using FOR loop.
CREATE PROCEDURE foreach_proc() LANGUAGE SQLSCRIPT AS
v_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;
