Clarification on SQL vs. SQLScript vs. CE Functions

[Former Member]

Hello,

Can someone explain the difference between "pure" SQL vs. SQLScript vs. CE Functions in SQLScript?

-Can you have SQLScript without using any CE functions? Does a calculated view with SQLScript but no CE functions become  "pure or native" SQL?

-Within a given graphical view, it it okay to use CE functions and ""pure or native" SQL for functionality not available with CE functions?

Thanks,

Mike

[rajarshi_muhuri · Accepted solution]

pure  sql are just statments like

select * from tableA

or insert in table values ( 'x', 'y', 1)

etc

SQL script are SQL enhanced by SAP for use in HANA, it makes good use of parallel multiprocessor capabilities . they run on the sql script engine , all the nice stuff about writing procedures etc run on this engine ,

and CE functions are specially optimised ,and run on the CE engine

so even though

SELECT A, B, C from "COLUMN_TABLE"

and

CE_COLUMN_TABLE("COLUMN_TABLE", [A, B, C])

do the same thing , CE functions are pre optimised and give faster results , but their capabilities are limited , whereas for SQLscript .. sky  is the limit

but for performance its advised not to mix sql script and CE functions then the operation hops two engines .

[Former Member · Accepted solution]

Hi Rajarshi Muhuri,

So does the use of SQLscript mean that it's only comprised of CE functions? If not, then how is SQLscript WITHOUT CE functions different from simple SQL in a scripted calculated view?

Thanks,

Mike

[Former Member · Accepted solution]

Hi Mike,

Sorry to jump in here... if you were look at the following statements:

a.  BEGIN

       tab_var = select col1 from <schema>.<table>;

     END;

b. BEGIN

      tab_var = CE_COLUMN_TABLE ("<schema>.<table>",["Col1"]);

    END;

Technically both (a) and (b) above qualify as SQLscripts and both will give you the same result except that only the latter uses a CE function.

CE Functions are nothing but pre-built functions delivered by SAP that can be used within SQLscripts to replace what you would have otherwise done with simple SQL. Since CE functions are optimised well and hits the calculation engine straight away, it could perform better than a  normal SQL statement. Also with SQL, a lot depends on how a person writes the specific SQL - depending on his or her level of knowledge & experience it could either be well optimised or really bad performing. With CE functions, you are taking away that possibility as you are forced to write it in a specific syntax which behind the scenes would create the most optimized SQL.

You could have an SQLscript without having any CE functions on it. However for best performance, try not to mix up normal SQL and CE functions in a single procedure/scripted cal view.

Thanks,

Anooj

[Former Member · Accepted solution]

Hi Anooj,

Thanks for the explanation. Just one follow-up: Is SQLscript without CE functions (e.g. none available for need) SQL or SQLscript or is that moot?

Mike

[Former Member · Accepted solution]

SQLscript without CE functions is still SQLscript. Similary SQLscript without SQL or CE functions is still SQLscript. Remember you could have more than just SQL statements or CE functions within a SQLscript like cursors, for loops, variables, calculations (e.g. a = b+ c) so on and so forth. An example:

create procedure <schema>.computesum(

          in a integer,

          in b integer,

          out c integer)

language sqlscript as

begin

c := a + b;

end;

The above procedure is also a SQLscript and it doesn't have a single SQL or CE function.

Thanks,

Anooj

[rajarshi_muhuri · Accepted solution]

beautiful answer .. anoooj

anyway i posted a sqlscript question for you ,if you could kindly answer

[Former Member · Accepted solution]

Is it possible to write SQL script with SQL statement with CE statement like

/********* Begin Procedure Script ************/

BEGIN

      select_comp2 = select COMPANYNAME from "SOMNATH"."COMPANIES2";    

     select_comp3 =  CE_COLUMN_TABLE ("SOMNATH"."COMPANIES3",[COMPANYNAME]);

     var_out = CE_UNION_ALL (:select_comp2,:select_comp3);

END /********* End Procedure Script ************/

[Former Member]

Hi Mike,

SQL is a single statement which may be DDL or DML where as PLSQL is a programing language made up of  no. of SQL statements, conditions, it also having variables etc.

and for difference in CE function and SQL follow the link below

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/6056911a-07cc-2e10-7a8a-ffa9b8cf5...

-Ruchi

[rajarshi_muhuri]

Ruchi has summed it up   . just read SQLSCRIPT instead of PLSQL

and (i could be wrong)  I dont think SQLSCRIPT can do DDL ( i.e write to a table )

[Former Member]

Hi Rajarshi,

Regarding your last question, the default of a procedure is to be read-only when using the GUI (New -> procedure). You can write your own CREATE PROCEDURE - SQL statement, which will be read-write.

Regards,

Denis