Getting the SQL Script behind a Graphical Calculation View

[rehanshaikh]

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.

[rehanshaikh · Accepted solution]

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

[Former Member · Accepted solution]

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

[lbreddemann · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[lbreddemann]

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).

[Former Member]

Hi Rehan,

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

-Avinash

[rehanshaikh]

Thanks Avinash, this was definitely helpful.