Adding objects to package or delivery unit

[iwan_santoso4]

Hi All,

I am creating a stored procedure using SQL Editor since I could not figure out adding "with result view" keyword. However when I did this, I created the procedure against a schema, not a package. Is there a way to include such object into a package or directly to delivery unit?

Thanks,

Iwan

[schneidertho · Accepted solution]

Hi Iwan,

I am not 100% if that would fit your purpose - but what about a scripted calculation view?

That should technically create a procedure (wrapped by a column view).

Best regards

Thorsten

[iwan_santoso4 · Accepted solution]

Hi Thorsten,

Thanks for the hints. It works for my purpose. I do have a follow up question, is there a way to define output structure by referring to existing table type?

in SQL Editor, i can specify (out out_var <existing table type>) where as when I create the procedure normally, I have to define each fields and its data type one by one.

Regards,

Iwan

[henrique_pinto]

Which revision of HANA are you using?

In more recent SPS3 & SPS4 releases, you can create a Procedure under a Package.

Just right click the Package and go to "New" menu, you should see the "Procedure" option.

Anyways, why couldn't you use the "WITH RESULT VIEW" statement?

Check the SQLScript guide if you have any issues:

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

[iwan_santoso4]

Henrique,

I can use "WITH RESULT VIEW", but I have to create them using SQLEditor and when I create procedure with SQL Editor, I can't assign it to a package.

When you do the right click on the package method, I could not add the key word "WITH RESULT VIEW".

The work around suggested by Thorsten serve the purpose, but the draw back is that we have to define the output structure field by field instead referring to an existing table type (see my follow up question for Throsten mentioned above).

By the way, I'm using SPS4 Rev 35.

[henrique_pinto]

I can use "WITH RESULT VIEW", but I have to create them using SQLEditor and when I create procedure with SQL Editor, I can't assign it to a package.

Of course you can't!

"Content Procedures with RESULT VIEW" are exactly what Calculation Views are.

When creating a Calc View, HANA will implicitly create a Column View that will be queried when you perform a SELECT on the Calc View. You can find it in the _SYS_BIC schema.

And in the same way you can't create a Procedure in a package with a table type, this will also apply to the Calc Views. HANA will implicitly create a Table Type. You'll also be able to see it in the _SYS_BIC schema.

Another "advanced" option you have to change these objects in a more efficient way is to edit the XML definition of the calc view manually. You can export the calc view in Developer Mode, which is nothing more than a descriptive XML. If you know your way around the XML schema, you can edit it directly and import it back into HANA, and then activate it.

[iwan_santoso4]

Hi Henrique,

Thanks for the tip on the 'advance' option (Export/Import Developer Mode). That's pretty neat.