Creating a CE function with dummy table

[Former Member]

Hi,

I have a code that works in sql form in a stored procedure (YEAR1 is an output parameter with 2 fields YEAR and HIKE).

DECLARE var1 INTEGER; 

SELECT YEAR(CURRENT_DATE) INTO var1 FROM DUMMY; 

YEAR1 = select "YEAR","HIKE" from HIKE_YEAR 

WHERE YEAR = :var1; 

Now I want to do this in CE functions. What will be the statements in CE for the above code?

I am confused on how to convert SELECT YEAR(CURRENT_DATE) INTO var1 FROM DUMMY;  into CE

Please help

[Former Member · Accepted solution]

Hi Lars,

Thanks for the reply. I am a beginner into HANA SQL and am going through a lot of SAP's own presentations on CE and other stuff.

As you can see in one of the slides I've pasted below.. I've been led to believe that CE is the holy grail of hana customizations.. and at this point I am able to write the SQL codes but am bothered by translating them to CE.. again the ppt says to not use SQL and CE together.. which adds to my frustrations

[lbreddemann · Accepted solution]

Hey Sammy

Ok, a formal comment to this would include things like:

• the slide doesn't state the HANA version it refers to, nor the time it has been created
• the SQL and the CE-functions are not equivalent (SQL performs UNION while CE-functions run a UNION AL
• the kind/volume of data this runs on is not mentioned
• the query logic is trivial here

And sure enough, if you actually go on today (e.g, on rev. 70) you can build an example where the CE-functions are faster.

The question now is: would your application user notice the difference (say 100 ms compared to 200 ms)?

And what's the reason for the difference? It's important to understand this.

For example in a test case I quickly build up, I've seen that the SQL query heavily uses parallel processing (even employs the OLAP engine for parallel aggregation).

The reason for it to be slower (it in fact had been this time) was the creation of intermediate result sets.

This might not be necessary in future revisions.

What I try to say is: never just believe rules-of-thumb when it comes to performance.

And also: don't waste time over optimizing parts that don't make up for a large share of your application response time.

Anyhow, since I guess you might be not happy at all about this answer, here is an answer to your original question.

By using an expression with the component() and the now() function you can extract the date part from the current timestamp.

BEGIN

    myt1 = CE_COLUMN_TABLE (T1, [ARTICLE_ID]);

  var_out = CE_PROJECTION (:myt1,

            [

            "ARTICLE_ID",

            CE_CALC ( 'component(now(), 1)', DATE ) as "MY_DATE"

            ]

            ) ;

END

Be aware that this example creates MY_DATE as a date data type, so you would get values like '01-01-2014' back. To just get the year number, use an integer data type.

- Lars

[Former Member · Accepted solution]

Thanks Lars.. Since I am quite new to this, I was going by what the slides said..

I really appreciate that you explained this in detail.

[rama_shankar3 · Accepted solution]

Good sample. Thanks Lars!

[rindia · Accepted solution]

Just now came to know about component() from you. Thanks Lars.

[former_member182302 · Accepted solution]

Hi Lars ,

I tried searching in different ways and different guides available online but couldn't find the function. Got to know about usage of Component () here.

Even the "Help" in HANA studio which I use more often only shows the below:

Are the other CE functions documented anywhere else?

Just wanted to know if there is any other variables other than ::SQL_ERROR_CODE and ::SQL_ERROR_MESSAGE as asked in this thread ( Like Error line ?? ) :

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Hey Krishna,

actually it is documented, but yes, very very hard to find .

Check the "Modeling guide - 8.6 Functions used in Expressions" to find the list of functions to be used in expressions.

For some reasons beyond my understanding the list of expression was maintained twice for the "Expression editor" and for the CE_CALC function and apparently the lists had not been kept in sync.

I hinted the documentation team to this, so this will be addressed.

Cheers,

Lars

[former_member182302 · Accepted solution]

Thanks for the update Lars

Regards,

Krishna Tangudu

[former_member182302]

I think you can add it as one of the limitations with CE functions library, i tried too but it didn't work for me earlier. It requires a existing "Column" table.

Tried even now in case if it works in the latest versions ( testing on Rev 70 ) it still failed with a similar error.

CE_COLUMN_TABLE

operator provides access to an existing column table. It takes the name of the table and returns its content bound to a variable. Optionally a list of attribute names can be provided to restrict the output to the given attributes

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

Thanks for the reply.

Can you suggest an alternate way in which I can create this CE function?

I mean the logic I need is - Select field A and B from a table which has a field called year also where that year = current year as per the system..

Again.. I need a CE logic for this.

Also, I've read somewhere that we should not mix CE and regular SQL logic.. but I think in some cases where CE can't suffice, SQL has to be used .. so do you think we should write pure SQLs in those cases or mixed CE-SQL scenarios? Which would perform better?

[lbreddemann]

Hey Sammy

Really, you don't "need" CE-functions.

It won't make your application a thousand times faster if you use CE-functions instead of SQL.

Using the standard DUMMY table with SQL will work perfectly fine. You won't see the performance difference (if there actually is any) for that one.

Sammy Salvatore wrote:
Also, I've read somewhere that we should not mix CE and regular SQL logic.. but I think in some cases where CE can't suffice, SQL has to be used .. so do you think we should write pure SQLs in those cases or mixed CE-SQL scenarios? Which would perform better?

If you read it somewhere it doesn't mean it's (still) true.

Try it out and build a working version using SQL. Measure the performance.

Only if you are not satisfied you might check for the reason and invest into building something faster.

Just a general remark here: developing on SAP HANA is not about getting the fastest "current_date".

It's all about developing correct and relevant applications that also happen to be rather fast - even without loads of tuning.

Get your requirements right. Understand the business logic and implement it correctly.

Then consider performance.

And, by the way: the vast majority of SAP HANA live content had been developed using SQL.

That was definitively not done, because we wanted to slow things down.

- Lars