Highlighted
Absent Member.
Absent Member.
301 views

HELP ! How to retrieve which sql instructions does a SBM Report

Hi all !

I'm a newbie and I guess if it's possibile to retrieve which sql instructions does a report on SBM.

I'm not so practice with SQL and so I need, instead of using advanced SQL option to create a report, to create a Report with all fields I need and then to see which SQL instructions this report uses.

This SQL instruction later will need to creat a specific view on DB Sql, so we'll use this view instead of report.

Thanks in advance.
0 Likes
5 Replies
Highlighted
Micro Focus Contributor
Micro Focus Contributor

Re: HELP ! How to retrieve which sql instructions does a SBM Report

Hello Simone. Using SQL as a newbie can be a daunting task. It would probably be easier to just create the reports within SBM and share links to the reports to those who need to see the data. Links can be shared to enable users to run the report dynamically or you can schedule reports which contain a snapshot of the data.

If you really wish to pursue the direct SQL approach, I recommend visiting the links below:
http://help.serena.com/doc_center/doc_center.html#sbm
SBM Database Schema Guide - http://help.serena.com/doc_center/sbm/ver11_5/sbm_schema_doc.pdf
This doc provides information on the SBM database schema.

You may also want to visit sites on SQL itself such as https://www.w3schools.com/sql/

Combining the information above with your knowledge of the process apps (maintained in SBM Composer) you can achieve your goal.

Garry
Highlighted
Absent Member.
Absent Member.

Re: HELP ! How to retrieve which sql instructions does a SBM Report

Hi Garry,

thank you very much for your detailed answer!

I'll have a look to links you provided.

Best regards
0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: HELP ! How to retrieve which sql instructions does a SBM Report

One trick I use is to select the "Advanced SQL" option in "Search Filter" then enter something that will to cause the query to fail. For example, "@where blah". This gets passed directly to the database server. Since it is not valid SQL, the report fails and it prints a message "an error occurred while processing the last request". The Windows Application Event log on the database server will contain an error which shows the full SQL for the report, including my defective "where blah" clause.

For reports where I am using "Basic Conditions" in the filter, I will add a "Join" to the CONTACTS table, select "This related item filters the report results", then enter a clause like "Last Name contains ' " (i.e. a single quote).
Highlighted
Absent Member.
Absent Member.

Re: HELP ! How to retrieve which sql instructions does a SBM Report

Hi Paul,

that's a great trick !!! 😉

I tried your first example and I got some query instructions in Event Log.
I put these instructions in Sql Developer but I got a query that doesn't give me the result I expect.
That's probably because using advanced Sql option all Basic Condition I've put in the report are ignored, isn't it?

So I tried with your second example, in which, if I've understood, you use an existing report and you want to extract what that report does exactly in Sql mode.
I added a Join (but I've not found CONTACTS in my selections, I've tried with some of our entries) and I put something like ' quote in the blank field.
In effect it cause a message error but something like "the string is not accepted" and in this case no sql instruction is printed in the event log.

So in my case the perfect soluction would be to get the exact SQL version of my existing Report to extract the column and the filter I've put in my selection but in SQL mode.

Why in the second case Join option doesn't give back in the log the SQL string even if the Report error occurred?

Thanks for your replies
0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: HELP ! How to retrieve which sql instructions does a SBM Report

Another option is to use SQL Profiler. If you run your existing report and check profiler and it will spell out the "where" clause, but won't show the column details that you have selected to be included in the report.

Note that Profiler will generate 100's or rows so it is a matter of scolling through them to find the row when the report was run.

Good luck.
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.