Simulate common table expression in HANA SQL Script

[Former Member]

Hi,

I need to simulate a recursive query in HANA stored Procedure.

I know that HANA does not support CTE, so how would I do this?

I looked at all the discussion posted, but I don’t see any viable solution at all.

Is there any way I can do this without creating other tables to load and unload data as in examples I've seen.

For example, if I have a table that contain Employee/Manager relationship.  I need to count number of employees under a manager when a manager number is given.  How can I accomplish this?

Thank you for your help in advance.

Hyun Grasso

[Former Member]

Since I was searching for an answer to that question and not found - after a couple of tries a more or less generic way to implement CTE's (recursive ones):

What have I done:

- Created a Procedure that creates a Column View (with associated type)

- The stored procedure reads the initial batch of data

- Then it reads recursively the next data - uniting it with the data read till then

- Then it returns the final dataset.

Our starting point were CTS's in SQL Server that had to be migrated to HANA. So we had a pattern that had to be reconstructed within HANA - and this approach works well for us..

CREATE PROCEDURE CompanyHierarchy(

  out act CompanyHierarchy_Type

) READS SQL DATA WITH RESULT VIEW CompanyHierarchyView

AS

BEGIN

  declare rc int := 0;

  declare lvl int := 0;

  actcte = 

  select :lvl as Level,

  CompanyID,

  CompanyDescription

  from Companies;

  SELECT ::ROWCOUNT INTO rc FROM DUMMY;

  while :rc > 0 and lvl < 100

  do

  lvl := :lvl + 1;

  actcte =

  select *

  from :actcte

  union all

         select  :lvl as Level,

  c.CompanyID,

  c.CompanyDescription

         from   CompaniesAssignment a

          inner join Companies c on c.CompanyID = a.CompanyID

                 inner join :actcte a1 on a1.AssignedCompanyCompanyID = a.CompanyID and a1.Level = :lvl - 1;

  SELECT ::ROWCOUNT INTO rc FROM DUMMY;

  end while;

    act = select * from :actcte

END;

In my case this worked quite well, hopefully it will help others to.

[lbreddemann]

Hi Hyun,

since CTEs can easily be expressed via subqueries that would be an approach you could follow.

The only thing you cannot easily do this way is recursive referencing of the the CTE by itself.

Typically this is done to deal with hierarchies or graph-like data structures.

If this is what you want to do (and what Jody already suspected), then SAP HANA hierarchy column views might be the right tool for you.

- Lars

[Former Member]

Hi.

the example in the document works greate!

I have few quick questions for you.

Whatt is the difference between column view and view in catelog?

Also is it true that you can only use the views in the sql script or stored procesdures, but not able to create attribure or analytic views?

Also what are some parameter options for hierachy definitionin column view?

Thanks!

Hyun

[Former Member]

I would like to throw in my two cents here. While it is true that CTE's can be expressed through sub queries, you can also theoretically ride your bike across Europe, and while that might be fun for someone who has the time and inclination without any deadlines to hit, it is not efficient or ideal when you could take a car or a plane.

For very complex queries, CTEs bring sanity back to developers, debuggers and support personel alike by allowing the query to be broken up in a clean, easy-to-think-about, and logical way. Once you have a named CTE, you can reference it as many times as needed in other CTEs in your query without duplicating subquery code all over the place, which just gets gross.

It's also true that you can use table variables in place of your CTE names that you can refer to with a minimal change of syntax, however the query optimizer does not seem to be able to handle this as well, because, I assume, it cannot see the entire idea and once and come up with as optimal of a plan. Just from my query I am working on, I can run my CTE in the SQL window in 30s, but when I break it out into individual table variables for a calculation view, it takes several minutes.

I can use CTE's just fine in the SQL window, but as soon as I try to create a view or a calculation view from them, HANA doesn't like it. Creating a calculated view even validates and activates, but then infuriatingly throws a syntax error when trying to query the calculation view.

[Former Member]

Love your analogy. Hope and team are listening to this for SPS10/11.

[lbreddemann]

I'm with you on the semantic and syntactic benefits of CTEs.

Your statement on the optimizer ability however is something I cannot second.

In fact with table variables the optimizer does have more options at hand to run complex data flows in parallel and eventually omit materialization or execution altogether.

None of the optimizers built into SAP HANA are "intend-aware" (would be nice, though). So, in your example it would be required to look into the actual case to find why the version with table variables was performing worse than the SQL CTE version.

- Lars

[Former Member]

I was just about to post question about CTE's in Hana, and searched a little.

Found this post and post from 2013:

I have to second this pleading for CTE's in Hana.

Many developers used to SQL development would find that helpful. In my MS SQL coding I have used recursive CTE's when it seemed that there's no other normal solution...in key moments I would say.

Also if I had to port my App from MS SQL DB to Hana I'd rather rewrite my procedure, change/adapt C# Data Layer and other changes would be minimal if names of procedures would stay the same. (in theory), then to make new hierarchy view etc.

I guess it can be done other ways in Hana, but lets make Hana as easy for developers to migrate to as possible.

CTE please !

Thx

[Former Member]

Hi,

Just let you know, CTE now works!  It might not be officially supported, but we were able to use CTE in our stored procedures and scripted views without recieving any errors.

Thanks,

Hyun

[Former Member]

Great news ! Thx 

What version are you running ?

Example of coding to make it work please ?

Or even better, please find time to write a Blog post about it so anyone who wants to try CTE's in Hana can know that it works !

Thx

[Former Member]

Sorry for the late reply.

We are on R84.

There are no differences in syntax in CTE created in SQL server vs.HANA.

However, you don’t have to use CTE in HANA for a lot of things as there are better ways in HANA as Lars pointed out, but I do still believe CTE is needed if you ever have to write recursive query.

I'll try to create a blog with examples as soon as I get a chance.

Thanks,

[Former Member]

Ok, thx. I have to upgrade first.

I like CTE's, am used to think in that way, whenever traversing thru hierarchy or any other recursion, CTE's are worth pure gold.

Thx

[lbreddemann]

In that case, brace yourself. CTEs in SAP HANA are currently (SPS 9) not supporting the recursive access.

[SergioG_TX]

Hi Hyun,

I am not sure if you created the blog but I was in the need of a sample for this. I was able to get mine working .. I am currently working on SP09 and I am also able to see the CTE from the plan viz perspective when SAP does the break down of steps and how long it takes to execute.

here is my example in case anyone needs it:

with y as (

  select col1, col2 ,coln

  from someTblOrView

)

select * from y;

hope this is helpful for others as well.

[Former Member]

Can you give more details?

My first inclination is to recommend you stay tuned for updates with SP7 - there may well be functionality there for this.

From an imperative logic perspective, you could also consider a WHILE loop - sometimes you can implement the same logic this way.

There is some basic functionality documented in the SQLScript guide. Open it up and search for "HIERARCHY COLUMN VIEW" - you can create a parent/child hierarchy (which is done automatically when you define such a hierarchy against an attribute view) and execute a subtree() function against it, for example. (This is the only function doc'ed in the SQLScript guide, more should come with SP7.

Finally - and this actually should have been my first question - what are you trying to achieve? If you're wanting to aggregate some values at various levels of a parent-child hierarchy (which would be accomplished recursively in application code), consider defining a parent-child hierarchy in your Attribute or Calc View. Then report using a tool like Excel or Design Studio which supports MDX for on-the-fly drill up/down against such hierarchies.

Hope that helps a bit.

[Former Member]

Hi I am trying to achieve sometype calcuation at various level of a parent-chil chearchy.  If I create an hierachy in the views, they are only visible via MDX connection, so I won't be able to use it via Webi for example.  However, I might be able to do somting with this subtree() funtion you are talking about.  I'll try it and let you know.

Thank you for all your help.

[Former Member]

Hi Jody,

What document are you referring you when you say "SQLScript guide"?

I searched "SAP_HANA_SQL_Script_Reference", "SAP_HANA_SQL_and_System_Views_Reference_en" and even HANA Dev Guide, but could not find any reference to HIERARCHY COLUMN VIEW nor subtree() function.

Thanks,

[Former Member]

SQLScript Reference, Page 50

http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

[lbreddemann]

... alternatively, just press [F1] in the SAP HANA Studio. All the documentation books have meanwhile been included into onboard online help system.

Searchable, clickable and just where you need it

- Lars

[Former Member]

Thank you!  I'll try it and let you know how it works out.

Hyun

[Former Member]

In this case, you can create an attribute view for this with a parent-child hierarchy. Then you can do

SELECT COUNT(employee_id) AS number_of_reports WHERE manager_hierarchy=1234;

Regards,

John