cancel
Showing results for 
Search instead for 
Did you mean: 

Convert SQL to CE Function

Former Member
0 Kudos

How can i convert the below sql statement into Hana SQLSCRIPT. Can we use 3 tables in the CE_JOIN

select

e.ID,e.NAME,e.LAST_NAME,r.role,n.name,

                              ( 0.998 * e.ID - 1 ) as CALCU

from employee e,role r, new_employee n

where e.role_id = r.role_id and e.role_id = n.role_id

I need something like in this format.

CREATE PROCEDURE ProcWithResultView(IN lt_employee EMPLOYEE,IN lt_role ROLE, IN lt_new_employee NEW_EMPLOYEE,

OUT EMPLOYEE_ROLE EMPLOYEE_ROLE)

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW ProcView AS

BEGIN

EMPLOYEE_ROLE = CE_JOIN(:lt_employee,:lt_role,:lt_new_employee,["ROLE_ID"],["NAME","LAST_NAME","ROLE_ID","ROLE","0.998 * 'ID' -1" as CALCU]);

END;

SELECT * FROM ProcView WITH PARAMETERS

('placeholder'=('$$lt_employee$$','EMPLOYEE'),

'placeholder'=('$$lt_role$$','ROLE'),

'placeholder'=('$$lt_role$$','NEW_EMPLOYEE'));

Accepted Solutions (1)

Accepted Solutions (1)

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

CE_JOIN does not support more than two tables.  You need to join the first two into an intermediate table, then join the intermediate table with the last table using another CE_JOIN.  The engine will see this declaration and convert to an optimized execution path at runtime.  So don't think that because you have two statements, it will execute sequentially. 

Cheers,

Rich Heilman

lbreddemann
Active Contributor
0 Kudos

In addition to Rich's correct answer:

A JOIN always involves two tables. Never one, never three or more, but just two. That's how the operation is defined.

The option in SQL to chain several JOIN clauses in one SQL statement in fact is just that: a chain of two-sided (binary) JOIN instructions.

Concerning the possible palatalization the answer is of course: it depends.

Obviously there are parts in the processing of a join command, where it's required to put the pieces together and create one result set. As long as you're not dealing with partition-wise joins, this part is hard to enable for parallel processing (basically you'd have to chunk up your data horizontally for that == table partitioning).

But other parts like reading the relevant data from the base table structures in the first place can and will be executed in parallel.

In fact, when assigning the output of a CE_JOIN function to a table variable you are not telling the calc engine to create an intermediate result set.

Instead, by using the table variables you create data flow paths.

Upon execution/instantiation of the procedure/calculation model, the calc engine will figure out which parts can be worked upon in parallle and which parts require sequential data access.

Just what you get by the SQL optimizer as well.

- Lars

Answers (0)