Question Re: Getting the SQL Script behind a Graphical Calculation View in Technology Q&A

Getting the SQL Script behind a Graphical Calculation View

rehanshaikh — Fri, 25 Sep 2015 13:21:04 GMT

I want to see the SQL script that is generated when I run my graphical calculation. By this I do not mean the select statement that extracts data from the view, but the statement that gets data from the database itself which includes the names of tables used inside the calculation view. Does anyone know of a way to do this?

Thanks.

Re: Getting the SQL Script behind a Graphical Calculation View

Former Member — Tue, 29 Sep 2015 08:19:14 GMT

Hi Rehan,

You can get the information about tables used in the CV from explain plan or the visualize plan.

-Avinash

Re: Getting the SQL Script behind a Graphical Calculation View

lbreddemann — Wed, 30 Sep 2015 00:03:52 GMT

You can review activated information views ( analytic and calculation views ) via the context menu in SAP HANA studio.

E.g. for calculation views right click on them and select

then you get this:

A similar visualisation exists for analytical views (Open Join Viewer).

Re: Getting the SQL Script behind a Graphical Calculation View

rehanshaikh — Wed, 30 Sep 2015 06:17:48 GMT

Thanks for your replies. What I need is an SQL statement that contains not only table names but also join information, such that I can delete the view but still be able to run the SQL and get the same results. Something like:

SELECT TBL1.COL1, TBL2.COL1, TBL2.COL2

FROM TBL1, TBL2

WHERE TBL1.COL1 = TBL2.COL1

Instead of:

SELECT COL1, COL2

FROM CALVIEW

Is it possible?

Thanks

Rehan

Re: Getting the SQL Script behind a Graphical Calculation View

rehanshaikh — Wed, 30 Sep 2015 06:18:25 GMT

Thanks Avinash, this was definitely helpful.

Re: Getting the SQL Script behind a Graphical Calculation View

Former Member — Wed, 30 Sep 2015 06:36:50 GMT

Rehan,

Not sure why you need the SQL from the CV and then modify..But you can find the SQL Script for the Calc View when its created,by right clicking the CV in your _SYS_BIC schema--> Open Definition --> Create Statement tab.

-Avinash

Re: Getting the SQL Script behind a Graphical Calculation View

lbreddemann — Wed, 30 Sep 2015 06:38:54 GMT

Nope. The views are not defined as SQL and can not (in every case) be rewritten as SQL.

Re: Getting the SQL Script behind a Graphical Calculation View

Former Member — Wed, 30 Sep 2015 07:27:07 GMT

Hi Rehan,

To get the JOIN conditions applied in a view, you can use the system view CS_JOIN_CONDITIONS.

It will also tell you the type of JOIN applied along with the tables/columns.

Here is a sample query:

SELECT VIEW_NAME, TABLE_NAME1, COLUMN_NAME1,TABLE_NAME2, COLUMN_NAME2, JOIN_TYPE

FROM CS_JOIN_CONDITIONS

WHERE VIEW_NAME = 'YOUR_VIEW_NAME'

Regards,

Varun

Re: Getting the SQL Script behind a Graphical Calculation View

Former Member — Thu, 05 Dec 2019 13:24:51 GMT

Hi You got answer for your question? If yes please provide, I have same requirement.