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